Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Help with SWITCH() and performance issue

Hi,

 

I am encountering performance issues where my table takes a long time to load due to a measure. 

I have the table on the left which is the result of many calculations. I want to create a measure that is represented by the table S+1 on the right, it is exactly the same numbers but shifted the categories by 1. I.e. (AA1->AA2), (AA2->CAT1), (CAT1->CAT2) etc.


The function below actually has the desired outcome but I cannot find how to write a more efficient formula. Do you have any idea? I used the SWITCH() function but the performance is really slow due to having too many CALCULATE. 

 

Measure S+1 =
var AA2 = CALCULATE([Measure], DAT[Subcategory]="AA1")
var CAT1 = CALCULATE([Measure], DAT[Subcategory]="AA2")
var CAT2 = CALCULATE([Measure], DAT[Subcategory]="CAT1")
var CAT3 =  CALCULATE([Measure], DAT[Subcategory]="CAT2")
var CAT4 =  CALCULATE([Measure], DAT[Subcategory]="CAT3")
var CAT5 =  CALCULATE([Measure], DAT[Subcategory]="CAT4")
var CAT6 =  CALCULATE([Measure], DAT[Subcategory]="CAT5")
return
SWITCH(TRUE(),
SELECTEDVALUE(DAT[Subcategory])="AA1", BLANK(),
SELECTEDVALUE(DAT[Subcategory])="AA2", AA2,
SELECTEDVALUE(DAT[Subcategory])="CAT1", CAT1,
SELECTEDVALUE(DAT[Subcategory])="CAT2", CAT2,
SELECTEDVALUE(DAT[Subcategory])="CAT3", CAT3,
SELECTEDVALUE(DAT[Subcategory])="CAT", CAT4,
SELECTEDVALUE(DAT[Subcategory])="CAT5", CAT5,
SELECTEDVALUE(DAT[Subcategory])="CAT6", CAT6)

 

 


How I could shift subcategories in a second measure in a more efficient manner?

Regards,

Need Help

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    Anonymous , Try like

     

    Measure new=
    VAR SelectedSubcategory = SELECTEDVALUE(DAT[Subcategory])

    RETURN
    IF(
    SelectedSubcategory in{ "AA1" ,"AA2" , "CAT1" , "CAT2" , "CAT3" , "CAT4" , "CAT5" , "CAT6"},
    calculate([Measure], DAT[Subcategory] =SelectedSubcategory), // Default measure
    BLANK()
    )

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi amitchandak thank you for your reply.

       

      This is not the solution as I would like to do the following switch:

      measure2 AA2 = measure1 AA1,

      measure2 CAT1= measure1 AA2,

      measure2 CAT2 = measure1 CAT1, etc..

      Perhaps the code that I provided was a bit confusing due to the var names.

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Anonymous ,

     

    Please try:

    Measure S+1 =
VAR SelectedSubcategory = SELECTEDVALUE(DAT[Subcategory])
RETURN
    SWITCH(
        SelectedSubcategory,
        "AA1", BLANK(),
        "AA2", CALCULATE([Measure], DAT[Subcategory] = "AA1"),
        "CAT1", CALCULATE([Measure], DAT[Subcategory] = "AA2"),
        "CAT2", CALCULATE([Measure], DAT[Subcategory] = "CAT1"),
        "CAT3", CALCULATE([Measure], DAT[Subcategory] = "CAT2"),
        "CAT4", CALCULATE([Measure], DAT[Subcategory] = "CAT3"),
        "CAT5", CALCULATE([Measure], DAT[Subcategory] = "CAT4"),
        "CAT6", CALCULATE([Measure], DAT[Subcategory] = "CAT5")
    )

     

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum