assign points based on what range a values falls under

[nolos]

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.

[Anonymous · Accepted solution]

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

[Ashish_Mathur]

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.

[Ritaf1983]

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 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

[nolos]

@Ritaf1983 

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=28RvIJ7Pwbg

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[nolos]

@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