topic Re: Calculate Stage based on Value in DAX Commands and Tips

Calculate Stage based on Value

Anonymous — Tue, 06 Sep 2022 14:40:20 GMT

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

Re: Calculate Stage based on Value

tamerj1 — Tue, 06 Sep 2022 14:54:38 GMT

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

Re: Calculate Stage based on Value

v-yueyunzh-msft — Wed, 07 Sep 2022 06:17:23 GMT

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