Distinct Count on High-Cardinality Dimension Filtered by Fact in Fabric

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