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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
JamHam
Frequent Visitor

Issue with SWITCH Measure giving incorrect value

Hello,

 

I am attempting to create a SWITCH measure that will return a value for a specific group of Item/SKU numbers. But when I add the measure I want to use the SWITCH function for it is giving incorrect values.

 

SWITCH incorrect value.png

 

Below are the calculations for UPH and SWITCH (only included partial list of Items/SKUs).

 

UPH Calc =
DIVIDE(sum('devSKUSummary'[UnitsSold]),[All Traffic])*100
 
SWITCH - Select SKUs - UPH =
CALCULATE([UPH Calc],
SWITCH('devSKUSummary'[SkuNumber],
"12761", TRUE(),
"12762", TRUE(),
"12763", TRUE(),
"21282", TRUE(),
FALSE()
))

 

Goal is to have the UPH measures mirror each other. I am fairly new to Power BI and may be using the SWITCH funchtion incorrectly.

 

Thank you for the assistance.

1 ACCEPTED SOLUTION

Sorry, it should be like this:

 

SWITCH - Select SKUs - UPH =

VAR MySelection = SELECTEDVALUE('devSKUSummary'[SkuNumber])

RETURN 
SWITCH(MySelection,
"12761", CALCULATE([UPH Calc],'devSKUSummary'[SkuNumber]= "12761"),
"12762", CALCULATE([UPH Calc],'devSKUSummary'[SkuNumber]= "12762"),
"12763",CALCULATE([UPH Calc],'devSKUSummary'[SkuNumber]= "12763"),
"21282", CALCULATE([UPH Calc],'devSKUSummary'[SkuNumber]= "21282"),
BLANK()
)

 

Anyway, if it doesn't work, can you post here some dummy data to check?

View solution in original post

4 REPLIES 4
mlsx4
Super User
Super User

Hi @JamHam 

 

I have not really understood what you want to do, but if you want to retrieve the value only for selected SKU, then you could do something like this:

 

SWITCH - Select SKUs - UPH =
SWITCH('devSKUSummary'[SkuNumber],
"12761", CALCULATE([UPH Calc],'devSKUSummary'[SkuNumber]= "12761"),
"12762", CALCULATE([UPH Calc],'devSKUSummary'[SkuNumber]= "12762"),
"12763",CALCULATE([UPH Calc],'devSKUSummary'[SkuNumber]= "12763"),
"21282", CALCULATE([UPH Calc],'devSKUSummary'[SkuNumber]= "21282"),
BLANK()
)

 

JamHam
Frequent Visitor

Thank you for the reply. I am attempting to build a measure that pulls UPH for specific skus. Instead of using filters to get the same result. When I look at the sku level the SWITCH calc shows a different number than the UPH calc. Which I would expect the same number to be displayed for the SWITCH and UPH calc. So I am not really sure what would cause this issue.

 

I attempted to use your updated SWTICH Calc. But it is giving an error on the 'devSKUSummary' [SKUNumber]. Power BI states that it "Cannot Find Name [SKUNumber]". Additionally when I try to enter the calc manually the [SKUNumber] does not show up as an option when I type 'dev in the calc. It is only showing measures being able to be selected.

Sorry, it should be like this:

 

SWITCH - Select SKUs - UPH =

VAR MySelection = SELECTEDVALUE('devSKUSummary'[SkuNumber])

RETURN 
SWITCH(MySelection,
"12761", CALCULATE([UPH Calc],'devSKUSummary'[SkuNumber]= "12761"),
"12762", CALCULATE([UPH Calc],'devSKUSummary'[SkuNumber]= "12762"),
"12763",CALCULATE([UPH Calc],'devSKUSummary'[SkuNumber]= "12763"),
"21282", CALCULATE([UPH Calc],'devSKUSummary'[SkuNumber]= "21282"),
BLANK()
)

 

Anyway, if it doesn't work, can you post here some dummy data to check?

JamHam
Frequent Visitor

@mlsx4  Your latest Measure got me close to what I needed. I had to add the UPH Calc into the measure to get it to work. But now the results are reporting correctly. 

 

 

NEW - SWITCH - Select SKUs - UPH =

VAR MySelection = SELECTEDVALUE('devSKUSummary'[SkuNumber])
VAR UPH = DIVIDE(sum('devSKUSummary'[UnitsSold]),[All Traffic])*100
RETURN
SWITCH(MySelection,
"12761", CALCULATE(UPH,'devSKUSummary'[SkuNumber]= "12761"),
"12762", CALCULATE(UPH,'devSKUSummary'[SkuNumber]= "12762"),
"12763", CALCULATE(UPH,'devSKUSummary'[SkuNumber]= "12763"),
"21282", CALCULATE(UPH,'devSKUSummary'[SkuNumber]= "21282"),
BLANK()
)
 
New SWITCH calc.png
 
 
Thank you again for your help and have a great weekend!

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.

Top Solution Authors