I am using PowerBI Desktop Version: 2.80.5803.1061 64-bit (April 2020) with Azure Data Explorer data source in DirectQuery mode. No issues with adding tables from the data source. I run into issues when creating relationships between my fact table and dim tables. I am trying to create a many to one relationship but it won't allow that becuase of cardinality issues. It forces me to create a many to many relationship. I have verified there are only distinct keys in the dim table without any null/spaces for keys. I have also verified the foreign keys in my fact table, all keys are in fact in the dim table. So there are no issues with the tables and data. I did some research in Kusto, looking at the queries PowerBI Desktop is executing to determine the cardinality of my tables. I believe I have found the issue there. PowerBI Desktop is using the Kusto dcount function. The Kusto documentation states that this function returns an estimate of distinct values. I verified this on a couple of dim tables. dcount does return the correct number when my dim table only has a few hundred records. But when my dim table has tens of thousands for records dcount isn't returning the correct number of distinct values. Below is a query PowerBI Desktop is executing to determine the cardinality of my dim table using dcount. This seems to be an issue which isn't allow me to create many to one relations between two tables with large amounts of records in them.
["DimGroup"] | project ["GroupKey"] | extend ["6a485aba16bf4cca8946e8165053ffa1..1"]=iff(isnull(["GroupKey"]), toreal(1.0), toreal(0.0)) | summarize ["a0"]=count(), ["a1"]=(dcountif(["GroupKey"], isnotnull(["GroupKey"]))) + (max(["6a485aba16bf4cca8946e8165053ffa1..1"]))
3 Comments
I am also facing the similar problem with Azure Data Explorer using DirectQuery is giving me many-to-many relationship, If the same table I import it can connect as one-to-many. Is there anything which is stopping this in directquery please ? Just to add up I am also using Power BI desktop Version: 2.80.5803.1061 64-bit (April 2020)
artemusMicrosoft Employee
As a workaround you can initially limit the rows to 1, create the relationiship, then remove that condition from direct query.
Note: This can also be legatimatly caused if your key column:
1. Has a blank value
2. Has values that differ only in case. The imported model is case insensitive when it comes to relationships.
I am facing a similar issue where I am getting incorrect results for a few of my aggregations due to this very reason.
If I create a DAX measure saying COUNTROWS(GROUPBY(‘TableName’, TableName[Column1])) , the query PowerBI is sending across to kusto in a direct query mode is:
TableName
| project ["Column1"]
| extend ["a4d6dd2f19144348a4cb982d7395c930..1"]=iff(isnull(["Column1"]), toreal(1), toreal(0))
| summarize ["a0"]=(dcountif(["Column1"], isnotnull(["Column1"]))) + (max(["a4d6dd2f19144348a4cb982d7395c930..1"]))According to the Kusto Docs the dcountif() function which we see above returns an estimate number and not the exact result, which might be the reason I am not getting the correct results.
How can this be fixed ?
