Help with SWITCH() and performance issue

[lBryan]

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,

[Anonymous]

Hi @lBryan ,

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

[amitchandak]

@lBryan , 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()
)

[lBryan]

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.