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
The best option is to create an Aggregation table in the Fabric Warehouse and then use this table in the Semantic Model.
In your Microsoft Warehouse, create a new table that groups by the attributes you typically filter or group by and pre-calculates the distinct count.
Please find the reference link - https://learn.microsoft.com/en-us/fabric/enterprise/powerbi/aggregations-auto
https://www.sqlbi.com/tv/aggregations-in-power-bi/
Example
CREATE TABLE fact_customer_distinctcount_agg AS
SELECT Cast(f.orderdate AS DATE) AS OrderDate,
p.productcategory,
g.region,
Count(DISTINCT f.customerid) AS DistinctCustomerCount
FROM facttable f
JOIN dimproduct p
ON f.productid = p.productid
JOIN dimgeography g
ON f.geographyid = g.geographyid
GROUP BY Cast(f.orderdate AS DATE),
p.productcategory,
g.region;
Hi PijushRoy
Thank you for your prompt reply.
I would like to avoid any changes in the Warehouse.
Do you believe there is no alternative, even with a basic star schema, to execute this distinct count efficiently?
Ideally, I am looking for a way to direct the engine to perform an inner join between the fact and the dimension after applying filters that reduce the fact table size.
Thank you for your guidance.