Forum Discussion
assign points based on what range a values falls under
I have a table with ranges in it. Depending on what range that the value I have falls under, I want to assign it a point value. Can you help with the DAX?
So if I have a value of 2.75 I want to retuen the value of 3 from the points column
I'm hoping not to have to use a switch.
Thanks in advance.
Hi nolos ,
Should the result be 2? If yes, how do you want to display it?
The creation of calculated column Ashish_Mathur mentioned is a great way to do this, and you can also try measure.
Here's how to get a result by creating a measure.
Measure 4 = CALCULATE(MAX('Table (2)'[Points]),FILTER('Table (2)','Table (2)'[Points High]>=SELECTEDVALUE('Table (3)'[Value])&&'Table (2)'[Points Low]<=SELECTEDVALUE('Table (3)'[Value])))My Table(2).
My Table(3).
I add a Slicer with Value column of Table(3) and a card with Measure4. When I select 2.75, the card shows 2.
Best regards,
Mengmeng Li
6 Replies
Hi,
Shouldn't the answer be 2? This calculated column formula work?
P = calculate(max(data[points]),filter(data1,data[Points low]<=earlier(data[value])&&data[Points high]>=earlier(data[value])))Hope this helps.
Hi nolos
If i understood you correctly and the goal is segmentation then please refer to the linked blog post:https://www.daxpatterns.com/static-segmentation/
If it is something elseplease share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.
How to Get Your Question Answered Quickly
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
Ritaf1983 Thank you. This is a great answer for if I get into a situation where I am working with each row in a table. This situation is different though. I have a visual like this
The sync Avg column in the visual is from this measure.
Sync Avg = CALCULATE(COUNT(drug_log[Cycle]), drug_log[Cycle]="Yes")/[Avg Rx Cost]
I just want to take the result of that measurement (eg. 2.24) and see where it fits in those ranges and get the point value.
I could do a switch and manually check:
switch(True,
Sync Avg >=0 && Sync Avg <= 1.49, 5,
Sync Avg >=1.5 && Sync Avg <= 1.99, 4,
Sync Avg >=2 && Sync Avg <= 2.49, 3,
Sync Avg >=2.5 && Sync Avg <= 2.99, 2,
Sync Avg >=3 && Sync Avg <= 3.49, 1,
Sync Avg >=3.5 && Sync Avg <= 3.99, 0)
But i figured there was an easier way and a way that would help me to not have to manually update the switch function each time the ranges change
That's good info but I don't think it applies to this one. Here is my visual.
Here is the measure I am using to get the Sync Avg
Sync Avg = CALCULATE(COUNT(drug_log[Cycle]), drug_log[Cycle]="Yes")/[Avg Rx Cost]
I just want to use the Sync Avg value to get the point value from the range table above. I could use the switch below but I don't want to have to manually update the switch values each time the range changes.
Switch(True,
Sync Avg <=0 && <= 1.49, 5
Sync Avg <=1.5 && <= 1.99, 4
Sync Avg <=2 && <= 2.49, 3
Sync Avg <=2.5 && <= 2.99, 2
Sync Avg <=3 && <= 3.49, 1
Sync Avg <=3.5 && <= 3.99, 0
Thoughts? Thanks.
Hi nolos
This is dynamic segmentation :
https://www.youtube.com/watch?v=28RvIJ7PwbgIf this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
Hi nolos ,
Should the result be 2? If yes, how do you want to display it?
The creation of calculated column Ashish_Mathur mentioned is a great way to do this, and you can also try measure.
Here's how to get a result by creating a measure.
Measure 4 = CALCULATE(MAX('Table (2)'[Points]),FILTER('Table (2)','Table (2)'[Points High]>=SELECTEDVALUE('Table (3)'[Value])&&'Table (2)'[Points Low]<=SELECTEDVALUE('Table (3)'[Value])))My Table(2).
My Table(3).
I add a Slicer with Value column of Table(3) and a card with Measure4. When I select 2.75, the card shows 2.
Best regards,
Mengmeng Li
