Help on Fixing measure issue using Switch Statement

[kxj54590]

Hi Everyone,

I am trying to solve a measure which has to dynamically caluclate the multiple bin rating based on the switch statement. In the below given file , I have created multiple parameters based on each product and with their product specific dynamic rates. Now, I have created a measure that has those multiple parameters ingested, however, the output is being static was wondering if i anybody can assist me with resolving this issue. 

Link Pbix file: 

https://ucmo0-my.sharepoint.com/:u:/g/personal/kxj54590_ucmo_edu/EUvDopn7C8xFlfPNLB587jwBsJ56G4lynOr...

Thanks

Kxj

[PhilipTreacy · Accepted solution]

Hi @kxj54590 

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

[kxj54590]

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.

Thanks

Kxj