topic DAX Optimization SUMX ( SUMMARIZE ) - Performance Issue in DAX Commands and Tips

DAX Optimization SUMX ( SUMMARIZE ) - Performance Issue

rishari — Tue, 04 Apr 2023 13:57:46 GMT

I'm not able to optimize this measure. I'm new to Power BI DAX. Kindly suggest me a way to run this DAX faster.

If I am wrong, please suggest me any other alternative way to achieve the below DAX

DAX Measure:
Measure name =
SUMX (
SUMMARIZE (table_name',
table_name'[Col1],table_name'[Col2],table_name'[col3],table_name'[col4],table_name'[col5],

"result",
CALCULATE (
DISTINCTCOUNTNOBLANK ( table_name'[col1] ),
FILTER (
table_name',
SUM ( table_name'[Counter] ) = 1
)
)
),
[result]
)

Re: DAX Optimization SUMX ( SUMMARIZE ) - Performance Issue

Greg_Deckler — Tue, 04 Apr 2023 14:14:18 GMT

rishari Can you post sample data so can test and optimize.

Re: DAX Optimization SUMX ( SUMMARIZE ) - Performance Issue

johnt75 — Tue, 04 Apr 2023 14:15:43 GMT

Its not best practice to use SUMMARIZE to add calculated columns, its better to use ADDCOLUMNS, e.g.

MyMeasure = SUMX ( ADDCOLUMNS ( SUMMARIZE ( 'table_name', 'table_name'[Col1], 'table_name'[Col2], 'table_name'[col3], 'table_name'[col4], 'table_name'[col5] ), "result", CALCULATE ( DISTINCTCOUNTNOBLANK ( 'table_name'[col1] ), FILTER ( 'table_name', SUM ( 'table_name'[Counter] ) = 1 ) ) ), [result] )

Re: DAX Optimization SUMX ( SUMMARIZE ) - Performance Issue

tamerj1 — Tue, 04 Apr 2023 14:30:43 GMT

rishari

Please try

MeasureName =
SUMX (
SUMMARIZE (
FILTER ( table_name, table_name[Counter] = 1 ),
table_name[Col1],
table_name[Col2],
table_name[col3],
table_name[col4],
table_name[col5],
"result", COUNTROWS ( VALUES ( table_name[col1] ) )
),
[result]
)

Re: DAX Optimization SUMX ( SUMMARIZE ) - Performance Issue

rishari — Tue, 04 Apr 2023 15:07:22 GMT

johnt75 - I need a single value to add in the matrix.

Re: DAX Optimization SUMX ( SUMMARIZE ) - Performance Issue

rishari — Tue, 04 Apr 2023 15:08:09 GMT

Hi tamerj1 - I tried this, but instead of numbers, I'm getting blank

Re: DAX Optimization SUMX ( SUMMARIZE ) - Performance Issue

rishari — Tue, 04 Apr 2023 15:09:34 GMT

Greg_Deckler - Sorry, I can't. It is confidential

Re: DAX Optimization SUMX ( SUMMARIZE ) - Performance Issue

tamerj1 — Tue, 04 Apr 2023 15:25:10 GMT

rishari

Please try

MeasureName =
SUMX (
FILTER (
SUMMARIZE (
table_name,
table_name[Col1],
table_name[Col2],
table_name[col3],
table_name[col4],
table_name[col5],
"@Result", COUNTROWS ( VALUES ( table_name[col1] ) ),
"@Counter", SUM ( table_name[Counter] )
),
[@Conter] = 1
),
[@Result]
)

Re: DAX Optimization SUMX ( SUMMARIZE ) - Performance Issue

johnt75 — Tue, 04 Apr 2023 15:27:25 GMT

This will return a single value, its just an optimized version of your code.

Re: DAX Optimization SUMX ( SUMMARIZE ) - Performance Issue

rishari — Wed, 05 Apr 2023 04:52:22 GMT

Hi tamerj1 - I'm getting the value now. It loads much faster. But still we have more granularity, hence it is not loading in the matrix. It takes much time

Re: DAX Optimization SUMX ( SUMMARIZE ) - Performance Issue

tamerj1 — Wed, 05 Apr 2023 04:58:47 GMT

rishari
Try

MeasureName = SUMX ( SUMMARIZE ( table_name, table_name[Col1], table_name[Col2], table_name[col3], table_name[col4], table_name[col5], "@Result", COUNTROWS ( VALUES ( table_name[col1] ) ), "@Counter", SUM ( table_name[Counter] ) ), IF ( [@Conter] = 1, [@Result] ) )

Re: DAX Optimization SUMX ( SUMMARIZE ) - Performance Issue

rishari — Tue, 11 Apr 2023 16:09:59 GMT

tamerj1 johnt75 Greg_Deckler -

I have created a temporary column using concat and counter column in the dataset.

temp_column = table_name[col1], table_name[col2]. table_name[col3], table_name[col4], table_name[col5]
Counter = 1 if the couter sum = 1 else 0

And I updated this DAX.
CALCULATE(
DISTINCTCOUNTNOBLANK ( TableName [Temp_column] ),
KEEPFILTERS ( tableName [Counter] = 1 )
)

Still, I'm facing the issue. The value is not matching. DAX is really difficult, please help me on this.