topic Re: Help with optimizing DAX measure in DAX Commands and Tips

Help with optimizing DAX measure

Anonymous — Fri, 26 Nov 2021 14:42:50 GMT

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 7 seconds everytime you click on something. I tried to do it with two different formulas :

KPI 1 = SUMX(DISTINCT(SUMMARIZE(FILTER('Fact_Table','Fact_Table'[level]="Low"),'Fact_Table'[id_cv],'FACT_Table'[Server_id])),1)

KPI 2 = countrows(SUMMARIZE(FILTER('Fact_Table','Fact_Table'[level]="Low"),'Fact_Table'[id_cv],'Fact_Table'[Server_id]))

but the response time is almost the same which is 7 seconds.

anyone have a best prosposition?

Any help with this would be appreciated.

Thank you

Re: Help with optimizing DAX measure

smpa01 — Fri, 26 Nov 2021 15:12:37 GMT

Anonymous 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

Re: Help with optimizing DAX measure

Anonymous — Fri, 26 Nov 2021 14:44:59 GMT

smpa01 thanks for your answer.

Unfortunately no, the response time is the same: 6 seconds

Re: Help with optimizing DAX measure

Jihwan_Kim — Fri, 26 Nov 2021 15:00:41 GMT

Hi,

Please try the below.

KPI 2 =
COUNTROWS (
CALCULATETABLE (
SUMMARIZE ( 'Fact_Table', 'Fact_Table'[id_cv], 'Fact_Table'[Server_id] ),
FILTER ( 'Fact_Table', 'Fact_Table'[level] = "Low" )
)
)

Re: Help with optimizing DAX measure

Anonymous — Fri, 26 Nov 2021 15:13:40 GMT

Hello Jihwan_Kim ,

Unfortunately, it takes too much time to compute 21seconds

Re: Help with optimizing DAX measure

PaulOlding — Fri, 26 Nov 2021 15:16:32 GMT

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/

Re: Help with optimizing DAX measure

smpa01 — Fri, 26 Nov 2021 15:21:26 GMT

Anonymous I second with PaulOlding; as much as any DAX enthusiast would love optimizing an inefficient measure, 6sec for a 200 mil dataset does not sound unreasonable unless you bring an aggregated table from the server and run the measure which will astronomically reduce the evaluation time.

Re: Help with optimizing DAX measure

Anonymous — Fri, 26 Nov 2021 15:28:06 GMT

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.

Re: Help with optimizing DAX measure

AlexisOlson — Sat, 27 Nov 2021 02:54:39 GMT

Do 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] )