Forum Discussion

rishari's avatar
rishari
Frequent Visitor
3 years ago

DAX Optimization SUMX ( SUMMARIZE ) - Performance Issue

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

DAX Optimization
Performance Issue
SUMMARIZE. SUMX

11 Replies

  • johnt75's avatar
    johnt75
    Super User
    3 years ago

    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]
)
      • johnt75's avatar
        johnt75
        Super User
        3 years ago

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

  • tamerj1's avatar
    tamerj1
    Community Champion
    3 years ago

    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's avatar
      rishari
      Frequent Visitor
      3 years ago

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

      • tamerj1's avatar
        tamerj1
        Community Champion
        3 years ago

        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's avatar
    rishari
    Frequent Visitor
    3 years ago

    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.