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
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