Forum Discussion
Help with optimizing DAX measure
Hi Anonymous
Unfortunately these don't look like particularly inefficient queries to me. (Obviously without seeing the real data).
The immediate thing that springs to mind is an aggregation table, so the query can be run on a table with much less than 200m rows. https://docs.microsoft.com/en-us/power-bi/transform-model/aggregations-advanced
However, you'll immediately hit a limitation of agg tables: your 'Fact Table' would need to be in DirectQuery mode. This article explains a workaround for that: https://dax.tips/2019/11/15/creative-aggs-part-vi-shadow-models/
Solution SageHi Anonymous
Unfortunately these don't look like particularly inefficient queries to me. (Obviously without seeing the real data).
The immediate thing that springs to mind is an aggregation table, so the query can be run on a table with much less than 200m rows. https://docs.microsoft.com/en-us/power-bi/transform-model/aggregations-advanced
However, you'll immediately hit a limitation of agg tables: your 'Fact Table' would need to be in DirectQuery mode. This article explains a workaround for that: https://dax.tips/2019/11/15/creative-aggs-part-vi-shadow-models/
PaulOlding smpa01 : thank you both for your help i will try the solution with the aggregation table to decrease the data volume and reduce the response time.