DAX Optimization SUMX ( SUMMARIZE ) - Performance Issue

[rishari]

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

[rishari]

@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.

[tamerj1]

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

[rishari]

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

[tamerj1]

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

[rishari]

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

[tamerj1]

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

[johnt75]

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

[rishari]

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

[johnt75]

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

[Greg_Deckler]

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

[rishari]

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