Forum Discussion
Calculate Stage based on Value
Hello All,
I have a total value as an output and I need to assign it a stage depending on Upper bound and Lower bound values for that stage.
Can someone help me with THE DAX as I am struggling to understand what is the write command to be written
My total=3 so it should print "M"
| Stage | Lower bound | Upper bound |
| N | 1.0 | 1.5 |
| B | 1.5 | 2.5 |
| M | 2.5 | 3.5 |
| GP | 3.5 | 4.5 |
| Champion | 4.5 | 5.0 |
Hi, Anonymous
According to your description, you want to assign [Stage] to the corresponding Total based on your rating table. Right?
Here are the steps you can follow：
(1)This is my test data : ‘Level’ and ‘Score’
(2)We can create a measure : ‘Level_name’
Level_name = VAR _total = SUM ( 'Score'[score] ) VAR _level = MAXX ( FILTER ( 'Level', 'Level'[Lower bound] <= _total && _total <= 'Level'[Upper bound] ), [Stage] ) RETURN IF ( _level = BLANK (), "none level match", _level )(3)Then we can put the measure in the visual and other fields we need , then we can meet your need :
If this method can't meet your requirement, can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.
This is my test .pbix file : Level.pbix
Best Regards,
Aniya Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
2 Replies
- tamerj1
Community Champion
Hi Anonymous
please try
Stage = VAR TotalValue = [Total Value] VAR T1 = FILTER ( Stages, Stages[Lower bound] <= TotalValue && Stages[Upper bound] >= TotalValue ) RETURN MAXX ( T1, Stages[Stage] ) - v-yueyunzh-msft
Community Support
Hi, Anonymous
According to your description, you want to assign [Stage] to the corresponding Total based on your rating table. Right?
Here are the steps you can follow：
(1)This is my test data : ‘Level’ and ‘Score’
(2)We can create a measure : ‘Level_name’
Level_name = VAR _total = SUM ( 'Score'[score] ) VAR _level = MAXX ( FILTER ( 'Level', 'Level'[Lower bound] <= _total && _total <= 'Level'[Upper bound] ), [Stage] ) RETURN IF ( _level = BLANK (), "none level match", _level )(3)Then we can put the measure in the visual and other fields we need , then we can meet your need :
If this method can't meet your requirement, can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.
This is my test .pbix file : Level.pbix
Best Regards,
Aniya Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
