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

Issue in Dax (Caluclated column).

 

Hello everyone, 

 

I am a novice user in power bi, slowly upgrading myself to multiple scenarios, have recently came across a situation to resolve the output by random change in the rate value passed through a parameter. The issue over here is I have written a switch dax function and applied the logic, however, it gives me output as zero. 

Is there any other work around to get the desired results in the "dynamic_bin_column" ? I am attaching the document as well for the solution, please provide a direction/guidance.

 

Thanks everyone for your valuable time & effort !!

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

 

@PhilipTreacy, @parry2k @AlexPowers 

 

1 ACCEPTED SOLUTION
Syndicate_Admin
Administrator
Administrator

There @Syndicate_Admin@kxj54590

Download this PBIX file with code shown below

This calculation should be done with a Measure rather than a Column. Here's the DAX for the Measure

dynamic_bin_measure = SWITCH (

TRUE, 

Dyn_Rate[Dyn_Rate Value] = 0, 0,
    
Dyn_Rate[Dyn_Rate Value] <= SELECTEDVALUE(Input[2 Tier Range]) , 1,

Dyn_Rate[Dyn_Rate Value] > SELECTEDVALUE(Input[2 Tier Range]) &&  Dyn_Rate[Dyn_Rate Value] <= SELECTEDVALUE(Input[3 Tier Range]), 2,
    
Dyn_Rate[Dyn_Rate Value] > SELECTEDVALUE(Input[3 Tier Range]) &&  Dyn_Rate[Dyn_Rate Value] <= SELECTEDVALUE(Input[4 Tier Range]), 3,
 
Dyn_Rate[Dyn_Rate Value] > SELECTEDVALUE(Input[4 Tier Range]) &&  Dyn_Rate[Dyn_Rate Value] <= SELECTEDVALUE(Input[5 Tier Range]), 4,
    
Dyn_Rate[Dyn_Rate Value] > SELECTEDVALUE(Input[5 Tier Range]), 5,
  
0

)
    

However there is another problem. You are testing for values of [Dyn_Rate Value] which can be anything from 0 to 100. But all of the Tier values are between 0 and 1 so as soon as [Dyn_Rate Value] is greater than 0, all of your dynamic bin values will be 5.

tiers.png

You either need to adjust the Tier values, or adjust the values that [Dyn_Rate Value] can take, or divide [Dyn_Rate Value] by 100 to make it always be between 0 and 1.0.

In the file above I've divided [Dyn_Rate Value] by 100.

tiers2.png

Regards

Phil

View solution in original post

3 REPLIES 3
Syndicate_Admin
Administrator
Administrator

There @Syndicate_Admin@kxj54590

Download this PBIX file with code shown below

This calculation should be done with a Measure rather than a Column. Here's the DAX for the Measure

dynamic_bin_measure = SWITCH (

TRUE, 

Dyn_Rate[Dyn_Rate Value] = 0, 0,
    
Dyn_Rate[Dyn_Rate Value] <= SELECTEDVALUE(Input[2 Tier Range]) , 1,

Dyn_Rate[Dyn_Rate Value] > SELECTEDVALUE(Input[2 Tier Range]) &&  Dyn_Rate[Dyn_Rate Value] <= SELECTEDVALUE(Input[3 Tier Range]), 2,
    
Dyn_Rate[Dyn_Rate Value] > SELECTEDVALUE(Input[3 Tier Range]) &&  Dyn_Rate[Dyn_Rate Value] <= SELECTEDVALUE(Input[4 Tier Range]), 3,
 
Dyn_Rate[Dyn_Rate Value] > SELECTEDVALUE(Input[4 Tier Range]) &&  Dyn_Rate[Dyn_Rate Value] <= SELECTEDVALUE(Input[5 Tier Range]), 4,
    
Dyn_Rate[Dyn_Rate Value] > SELECTEDVALUE(Input[5 Tier Range]), 5,
  
0

)
    

However there is another problem. You are testing for values of [Dyn_Rate Value] which can be anything from 0 to 100. But all of the Tier values are between 0 and 1 so as soon as [Dyn_Rate Value] is greater than 0, all of your dynamic bin values will be 5.

tiers.png

You either need to adjust the Tier values, or adjust the values that [Dyn_Rate Value] can take, or divide [Dyn_Rate Value] by 100 to make it always be between 0 and 1.0.

In the file above I've divided [Dyn_Rate Value] by 100.

tiers2.png

Regards

Phil

@Syndicate_Admin@philiptreacy: Quick question, the way you have explained the scenario is outstanding and has given me the desired output. However, I am thinking into more advance scenario where i have it for multiple parameters. The current pbix has got single paramter passed across multuple product names. However, as i am thinking out loud, what if i pass each paramter rate value per product name/product ID. The reason behind thinking this kind of approach is because each product name/product ID has got disparate tier range values. So, if a user wants to punch in each rate, to each product, How can that be achieved dynamically ? Is there any possible option to solve this scenario ? 

I am excited to look forward to your thoughts/solution.

 

Thanks

Kxj

 

 

@Syndicate_Admin : Excellent, thanks for the work around. I really feel good in this community by solving problems. I hope one day i can give back the same. 🙂

 

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.