Forum Discussion

AS1110's avatar
AS1110
Frequent Visitor
4 years ago
Solved

Need help to optimize the AddColumns and Summarize DAX Measure

Hi Community,

I am having the below measure which will give the "Top Competitor" .Whenever I use this measure in the visual its causing high latnecy.It would be of great help if you guide me to optimise the below dax!! 🙂


Please note that I am using live connection type to AAS.

Top Competitor =
VAR TimeCalculation = SELECTEDVALUE('Time'[TimeCalculation])
VAR Manuf = FILTER(ADDCOLUMNS(SUMMARIZE(ALLSELECTED('Manufacturer'), [Manufacturer]),
                    "TotalValue",
                   CALCULATE(SUM('Sales'[Sales]),
                    ALL('Time'), 'Time'[Offset] = 0, 'Time '[TimeCalculation] = TimeCalculation, ALL(Brand))),
                  AND(NOT([Manufacturer]) IN {"abc", "xyz"}, NOT([TotalValue]=0)))
VAR Top1 = TOPN(1, Manuf, [TotalValue], DESC)
RETURN MAXX(Top1, [Manufacturer])

Expected Result:

CountryProductBrandTop Competitor
United Statesaaazzzpqr
    


Any help in this regard would be much appreaciated!!

Thank You


 

  • daXtreme's avatar
    daXtreme
    4 years ago

     

    // You can try this... but of course I have no access
// to your data, so it's only based on my "what I think
// I could do"... One can't really do any DAX tuning without
// access to the model/data. And your DAX will never be fast
// if the SQL it creates is not. You also have to have a look
// at the generated SQL and tune your tables accordingly.

Top Competitor =
VAR TimeCalculation =
    SELECTEDVALUE( 'Time'[TimeCalculation] )
var ManufacturersOfInterest =
    FILTER(
        ALLSELECTED( 'Manufacturer'[Manufacturer] ),
        NOT 'Manufacturer'[Manufacturer] IN {
            "abc",
            "xyz"
        }
     )
VAR Manuf =
    ADDCOLUMNS(
        ManufacturersOfInterest,
        "@TotalValue", // This will likely be a spoiler...
            CALCULATE(
                SUM( 'Sales'[Sales] ),
                'Time'[Offset] = 0,
                'Time '[TimeCalculation] = TimeCalculation,
                ALL( 'Time' ),
                ALL( Brand )
            )
    )
VAR Top1Manufacturer =
    MAXX(
        TOPN(
            1,
            FILTER(
                Manuf,
                [@TotalValue] <> 0 // Change to > 0 if possible
            ),
            [@TotalValue], DESC
        ),
        'Manufacturer'[Manufacturer]
    )
RETURN
    Top1Manufacturer

     

2 Replies

  • daXtreme's avatar
    daXtreme
    Icon for Solution Sage rankSolution Sage
    4 years ago

     

    // You can try this... but of course I have no access
// to your data, so it's only based on my "what I think
// I could do"... One can't really do any DAX tuning without
// access to the model/data. And your DAX will never be fast
// if the SQL it creates is not. You also have to have a look
// at the generated SQL and tune your tables accordingly.

Top Competitor =
VAR TimeCalculation =
    SELECTEDVALUE( 'Time'[TimeCalculation] )
var ManufacturersOfInterest =
    FILTER(
        ALLSELECTED( 'Manufacturer'[Manufacturer] ),
        NOT 'Manufacturer'[Manufacturer] IN {
            "abc",
            "xyz"
        }
     )
VAR Manuf =
    ADDCOLUMNS(
        ManufacturersOfInterest,
        "@TotalValue", // This will likely be a spoiler...
            CALCULATE(
                SUM( 'Sales'[Sales] ),
                'Time'[Offset] = 0,
                'Time '[TimeCalculation] = TimeCalculation,
                ALL( 'Time' ),
                ALL( Brand )
            )
    )
VAR Top1Manufacturer =
    MAXX(
        TOPN(
            1,
            FILTER(
                Manuf,
                [@TotalValue] <> 0 // Change to > 0 if possible
            ),
            [@TotalValue], DESC
        ),
        'Manufacturer'[Manufacturer]
    )
RETURN
    Top1Manufacturer

     

    • AS1110's avatar
      AS1110
      Frequent Visitor
      4 years ago

      Thank alot for the reply😊
      This solution works perfectly and solved the problem. 
      Cheers😀