cancel
Showing results for 
Search instead for 
Did you mean: 
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

View solution in original post

@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
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors