Forum Discussion

Helper IV

4 years ago

Solved

Dax help

hi all, I have to measure the difference between the two-column. Ex if my RCC works on level r13 then BW at least on level r10 but it is on level R04 need to highlight the gap on that row....

Need Help

Anonymous

4 years ago

Hi sameergupta60 ,

Please try:

Measure = 
var _maxLevel= MAXX(ALL('Table'),[Levels])
var _levelMinus3= RIGHT(_maxLevel,2)-3
var _LastBWDate=CALCULATE(LASTDATE('Table'[02 BW]),ALL('Table'))
var _LastBWLevel= CALCULATE( RIGHT( MAX('Table'[Levels]),2),FILTER(ALL('Table'),[02 BW]=_LastBWDate  ))
return  IF(MAX('Table'[Levels])="R"&FORMAT(_levelMinus3,"00") , IF(_levelMinus3> CONVERT(_LastBWLevel,INTEGER),"Red"))

Set Conditional formatting for [Levels] and [02 BW] based on the measure:

Output:

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

measure diff.pbix57 KB

Anonymous

Not applicable

4 years ago

Hi sameergupta60 ,

Got it. You mean there are multiple types of Levels, like start with R, start with T, start with A,start with B...

I'd suggest you seperate the Levels column to Level Type and Level Rank columns firstly. And since there is mo enough data in your sample file to test. I used mine instead.

Level Type = LEFT([Levels],1)

Level Rank = CONVERT( RIGHT([Levels],2) ,INTEGER)

Then create the measure for conditional formatting:

Measure 2 = var _maxLevel= MAXX(FILTER(ALLSELECTED('Table'),[Level Type]=MAX('Table'[Level Type])),[Level Rank])
var _levelMinus3= _maxLevel-3
var _LastBWDate=MAXX(FILTER(ALL('Table'), [Level Type]=MAX('Table'[Level Type]) &&[02 BW]<>BLANK()),[02 BW])
var _LastBWLevel= CALCULATE(MAX('Table'[Level Rank]),FILTER(ALLSELECTED('Table'), [Level Type]=MAX('Table'[Level Type]) && [02 BW]=_LastBWDate))
return IF(MAX('Table'[Levels])=MAX('Table'[Level Type]) & FORMAT(_LastBWLevel,"00"), IF(_levelMinus3> _LastBWLevel,"Red"))