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/
Super UserDo any of these columns exist in a dimension table or are they all exclusive to the 'Fact_Table'? Summarizing over dimensions is likely faster.
Is your situation such that you could use SUMMARIZECOLUMNS here?
KPI =
COUNTROWS (
SUMMARIZECOLUMNS (
'Fact_Table'[id_cv],
'Fact_Table'[Server_id],
TREATAS ( { "Low" }, 'Fact_Table'[level] )
)
)
You could also try counting one or the other summarized columns using DISTINCTCOUNT or SUMX:
KPI 1 =
SUMX (
SUMMARIZECOLUMNS (
'Fact_Table'[id_cv],
TREATAS ( { "Low" }, 'Fact_Table'[level] ),
"@Rows", CALCULATE ( DISTINCTCOUNT ( 'Fact_Table'[Server_id] ) )
),
[@Rows]
)
KPI 2 =
SUMX (
SUMMARIZECOLUMNS (
'Fact_Table'[Server_id],
TREATAS ( { "Low" }, 'Fact_Table'[level] ),
"@Rows", CALCULATE ( SUMX ( VALUES ( 'Fact_Table'[id_cv] ), 1 ) )
),
[@Rows]
)