Forum Discussion
Help on Fixing measure issue using Switch Statement
Hi Anonymous
All the Tier Range values are less than 0 but all your Rate values are at least 45.
So your SWITCH will only ever return 5
Multiple_Bin_measure_rate = SWITCH ( TRUE, BBC_Rate[BBC_Rate Value] = 0, 0, BBC_Rate[BBC_Rate Value] <= SELECTEDVALUE(Input[2 Tier Range]) , 1, BBC_Rate[BBC_Rate Value] > SELECTEDVALUE(Input[2 Tier Range]) && BBC_Rate[BBC_Rate Value] <= SELECTEDVALUE(Input[3 Tier Range]), 2, BBC_Rate[BBC_Rate Value] > SELECTEDVALUE(Input[3 Tier Range]) && BBC_Rate[BBC_Rate Value] <= SELECTEDVALUE(Input[4 Tier Range]), 3, BBC_Rate[BBC_Rate Value] > SELECTEDVALUE(Input[4 Tier Range]) && BBC_Rate[BBC_Rate Value] <= SELECTEDVALUE(Input[5 Tier Range]), 4, BBC_Rate[BBC_Rate Value] > SELECTEDVALUE(Input[5 Tier Range]), 5, CDE_Rate[CDE_Rate Value] = 0, 0, CDE_Rate[CDE_Rate Value] <= SELECTEDVALUE(Input[2 Tier Range]) , 1, CDE_Rate[CDE_Rate Value] > SELECTEDVALUE(Input[2 Tier Range]) && CDE_Rate[CDE_Rate Value] <= SELECTEDVALUE(Input[3 Tier Range]), 2, CDE_Rate[CDE_Rate Value] > SELECTEDVALUE(Input[3 Tier Range]) && CDE_Rate[CDE_Rate Value] <= SELECTEDVALUE(Input[4 Tier Range]), 3, CDE_Rate[CDE_Rate Value] > SELECTEDVALUE(Input[4 Tier Range]) && CDE_Rate[CDE_Rate Value] <= SELECTEDVALUE(Input[5 Tier Range]), 4, CDE_Rate[CDE_Rate Value] > SELECTEDVALUE(Input[5 Tier Range]), 5, Ram_Rate[Ram_Rate Value] = 0, 0, Ram_Rate[Ram_Rate Value] <= SELECTEDVALUE(Input[2 Tier Range]) , 1, Ram_Rate[Ram_Rate Value] > SELECTEDVALUE(Input[2 Tier Range]) && Ram_Rate[Ram_Rate Value] <= SELECTEDVALUE(Input[3 Tier Range]), 2, Ram_Rate[Ram_Rate Value] > SELECTEDVALUE(Input[3 Tier Range]) && Ram_Rate[Ram_Rate Value] <= SELECTEDVALUE(Input[4 Tier Range]), 3, Ram_Rate[Ram_Rate Value] > SELECTEDVALUE(Input[4 Tier Range]) && Ram_Rate[Ram_Rate Value] <= SELECTEDVALUE(Input[5 Tier Range]), 4, Ram_Rate[Ram_Rate Value] > SELECTEDVALUE(Input[5 Tier Range]), 5, 0 )because the only condition that is True is that the rates are greater than the value in 5 Tier Range
BBC_Rate[BBC_Rate Value] > SELECTEDVALUE(Input[5 Tier Range]), 5,Also, with SWITCH, it stops evaluating after it reaches the first True condition. So as things are with BBC_Rate at 45 minimum, none of the lines after this
BBC_Rate[BBC_Rate Value] > SELECTEDVALUE(Input[5 Tier Range]), 5,will ever get tested. You need to redesign your SWITCH or rethink the approach to what you are trying to do.
Regards
Phil
Hi Anonymous
All the Tier Range values are less than 0 but all your Rate values are at least 45.
So your SWITCH will only ever return 5
Multiple_Bin_measure_rate = SWITCH (
TRUE,
BBC_Rate[BBC_Rate Value] = 0, 0,
BBC_Rate[BBC_Rate Value] <= SELECTEDVALUE(Input[2 Tier Range]) , 1,
BBC_Rate[BBC_Rate Value] > SELECTEDVALUE(Input[2 Tier Range]) && BBC_Rate[BBC_Rate Value] <= SELECTEDVALUE(Input[3 Tier Range]), 2,
BBC_Rate[BBC_Rate Value] > SELECTEDVALUE(Input[3 Tier Range]) && BBC_Rate[BBC_Rate Value] <= SELECTEDVALUE(Input[4 Tier Range]), 3,
BBC_Rate[BBC_Rate Value] > SELECTEDVALUE(Input[4 Tier Range]) && BBC_Rate[BBC_Rate Value] <= SELECTEDVALUE(Input[5 Tier Range]), 4,
BBC_Rate[BBC_Rate Value] > SELECTEDVALUE(Input[5 Tier Range]), 5,
CDE_Rate[CDE_Rate Value] = 0, 0,
CDE_Rate[CDE_Rate Value] <= SELECTEDVALUE(Input[2 Tier Range]) , 1,
CDE_Rate[CDE_Rate Value] > SELECTEDVALUE(Input[2 Tier Range]) && CDE_Rate[CDE_Rate Value] <= SELECTEDVALUE(Input[3 Tier Range]), 2,
CDE_Rate[CDE_Rate Value] > SELECTEDVALUE(Input[3 Tier Range]) && CDE_Rate[CDE_Rate Value] <= SELECTEDVALUE(Input[4 Tier Range]), 3,
CDE_Rate[CDE_Rate Value] > SELECTEDVALUE(Input[4 Tier Range]) && CDE_Rate[CDE_Rate Value] <= SELECTEDVALUE(Input[5 Tier Range]), 4,
CDE_Rate[CDE_Rate Value] > SELECTEDVALUE(Input[5 Tier Range]), 5,
Ram_Rate[Ram_Rate Value] = 0, 0,
Ram_Rate[Ram_Rate Value] <= SELECTEDVALUE(Input[2 Tier Range]) , 1,
Ram_Rate[Ram_Rate Value] > SELECTEDVALUE(Input[2 Tier Range]) && Ram_Rate[Ram_Rate Value] <= SELECTEDVALUE(Input[3 Tier Range]), 2,
Ram_Rate[Ram_Rate Value] > SELECTEDVALUE(Input[3 Tier Range]) && Ram_Rate[Ram_Rate Value] <= SELECTEDVALUE(Input[4 Tier Range]), 3,
Ram_Rate[Ram_Rate Value] > SELECTEDVALUE(Input[4 Tier Range]) && Ram_Rate[Ram_Rate Value] <= SELECTEDVALUE(Input[5 Tier Range]), 4,
Ram_Rate[Ram_Rate Value] > SELECTEDVALUE(Input[5 Tier Range]), 5,
0
)
because the only condition that is True is that the rates are greater than the value in 5 Tier Range
BBC_Rate[BBC_Rate Value] > SELECTEDVALUE(Input[5 Tier Range]), 5,
Also, with SWITCH, it stops evaluating after it reaches the first True condition. So as things are with BBC_Rate at 45 minimum, none of the lines after this
BBC_Rate[BBC_Rate Value] > SELECTEDVALUE(Input[5 Tier Range]), 5,will ever get tested. You need to redesign your SWITCH or rethink the approach to what you are trying to do.
Regards
Phil
Hi PhilipTreacy Firstly, thanks for taking time in elobarating in detail with the issue that i encountered. Second, is there any other work around to this type of complex scenario modeling ?
Basically, I am trying to caluclate dynamically multiple bin measure rate (measure column) based on the tier ranges. I have created multiple parameters, for each product name which has different rates or to be more (i can say product specific rates). However, when i ingested those multiple parameters into my measure (multiple bin measure rate), not able to get the desired output. So, is there any other alternate approach to this kinda advance scenario modeling ? Is it possible to acheive it in power bi because i have never came across such kinda scenario modeling specific to each product name.
Please let me know your thoughts.
ThanksKxj