Calculate Stage based on Value

[Anonymous]

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

[v-yueyunzh-msft · Accepted solution]

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

[tamerj1]

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