Forum Discussion

nolos's avatar
nolos
New Member
2 years ago
Solved

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...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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

     

Ritaf1983's avatar
Ritaf1983
Icon for Super User rankSuper User
2 years ago

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's avatar
nolos
New Member
2 years ago

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