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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
kxj54590
Helper II
Helper II

Help on Fixing measure issue using Switch Statement

 

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

 

1 ACCEPTED SOLUTION
PhilipTreacy
Super User
Super User

Hi @kxj54590 

All the Tier Range values are less than 0 but all your Rate values are at least 45.

tierrange.png

 

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



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


View solution in original post

2 REPLIES 2
PhilipTreacy
Super User
Super User

Hi @kxj54590 

All the Tier Range values are less than 0 but all your Rate values are at least 45.

tierrange.png

 

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



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


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

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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