Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
lBryan
Frequent Visitor

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.

lBryan_0-1692282141763.png


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,

3 REPLIES 3
Anonymous
Not applicable

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
Super User
Super User

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

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

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.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.