Forum Discussion
assign points based on what range a values falls under
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
Super UserHi 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 else
please 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.
- Ritaf1983
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
