Blog Post

Azure Data Explorer Blog
2 MIN READ

Calculating distinct counts in Power BI with data in Kusto

DanyHoter's avatar
DanyHoter
Icon for Microsoft rankMicrosoft
Oct 02, 2023
  Summary Calculating distinct counts on massive distributed datasets is not trivial. Kusto (aka Azure Data Explorer/KQL database in Fabric) dcount and dcountif functions use a special algorith...
Published Oct 02, 2023
Version 1.0
ADX
DISTINCT
distinct count
Fabric
kusto
simonnuss's avatar
simonnuss
Copper Contributor
Feb 25, 2024

For anyone using Kusto in Power BI DirectQuery mode, read this article carefully.

 

In my scenario, I was unable to create a relationship between a dimension and a fact. When creating a relationship, Power BI must perform a COUNTROWS('Table') = DISTINCTCOUNT('Table'[PrimaryKey]) test. Obviously, a primary key column is distinct; however, this will fail with poor accuracy. In my case, setting it to -1 fixed the issue.