Forum Discussion
Help with optimizing DAX measure
Hello everybody, I have a Fact table that contains 200Millions rows I need to optimize this measure to improve response times ,the calculation of my measures takes too much time to compute (6 to...
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/
smpa01
Community Champion
Community ChampionAnonymous does this improve?
KPI 1 =
COUNTROWS (
FILTER (
SUMMARIZE (
'Fact_Table',
'Fact_Table'[level],
'Fact_Table'[id_cv],
'FACT_Table'[Server_id]
),
'Fact_Table'[level] = "Low"
)
)
or this
KPI1 =
COUNTROWS (
CALCULATETABLE (
SUMMARIZE ( 'Table', 'Table'[Server_id], 'Table'[id_cv], 'Table'[level] ),
KEEPFILTERS ( 'Table'[level] = "Low" )
)
)
Anonymous please test out the above two
smpa01 thanks for your answer.
Unfortunately no, the response time is the same: 6 seconds