Forum Discussion
Distinct Count on High-Cardinality Dimension Filtered by Fact in Fabric
Adding Aggregation table is the best approach.
~ at source (table)
~ at manage aggregations in Power BI
https://learn.microsoft.com/en-us/power-bi/transform-model/aggregations-advanced
goal: smaller aggregated fact table
I noticed that you have dimenion of 500 M rows, which is typically a lot for dimension table. Unless it is a master list of combinations (like bridge table).
Please see if you can reduce the size or row count in other means! again each requirement is different, just sharing my thoughts!Optional: Read: https://www.sqlbi.com/wp-content/uploads/Understanding-Distinct-Count-in-DAX-Query-Plans.pdf
Hi alexandra-pbi ,
With the data volumes you described, DirectQuery cannot perform a distinct count efficiently by joining the fact and dimension on the fly, even when the fact table is heavily filtered. The only reliable way to make this operation perform well is to use an aggregated table that precomputes the distinct count at the level your report needs.
If you prefer not to change the Warehouse, you can still create this aggregated table directly in the semantic model as an Import table and map it with Manage Aggregations. A dimension with five hundred million rows is also unusually large, so reducing its size where possible will help. There is no alternative setting or DAX technique that can make a direct distinct count over these tables run efficiently in DirectQuery without pre-aggregation.
Best Regards,
Tejaswi.
Community Support
Hi alexandra-pbi ,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.
Thank you.Hi alexandra-pbi ,
I wanted to follow up and see if you had a chance to review the information shared. If you have any further questions or need additional assistance, feel free to reach out.
Thank you.