Dax help

Any one have solution of above query please revert it is urgent

Hi sameergupta60 ,

Sorry for that the information you have provided is not making the problem clear to me. What's the meaning of

1.my RCC works on level r13,  [01 RCC] has date value when [Levels]="R13"?

2.then BW at least on level  r10,   need a value? for [02 BW] when [Levels]="R10"?

3. but it is on level R04 need to highlight  , based on your screenshot ,you directed to R05 not R04?

And from this: I have to measure the difference between the two-column. It seems that you want to calculate the diff of [Slab Cycle] between R13 and R04. 

R13 is the maximum level.  

R04 is R13 minus 9

If so, please try:

Max level -9 = var _maxLevel= MAXX(ALL('Table'),[Levels])
var _levelMinus9= RIGHT(_maxLevel,2)-9
return "R"&FORMAT(_levelMinus9,"00") 

Diff = 
var _maxLevel= MAXX(ALL('Table'),[Levels])
return  CALCULATE(SUM('Table'[Slab Cycle]),FILTER('Table',[Levels]=_maxLevel))- CALCULATE(SUM('Table'[Slab Cycle]),FILTER('Table',[Levels]=[Max level -9]))

For conditional formatting:

Color = IF(MAX('Table'[Levels])=[Max level -9],"Green") 

Output:

Or if it's not your expected, please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.

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.

Hi sameergupta60 ,

Try to modify:

var _LastBWDate=CALCULATE(LASTDATE('Table'[02 BW]),ALL('Table'))

to:

var _LastBWDate=MAXX(FILTER(ALL('Table'),[02 BW]<>BLANK()),[02 BW])

If it doesn't work , please share me with your pbix file after removing sensitive data.

Best Regards,
Eyelyn Qin

Hi sameergupta60 ,

Try:

Measure1 = var _maxLevel= MAXX(FILTER(ALLSELECTED('bar-1 (2)'),LEFT([Code82],1)="R"),[Code82]) 
var _levelMinus3= RIGHT(_maxLevel,2)-3 
var _LastBWDate=MAXX(FILTER(ALLSELECTED('bar-1 (2)'),[02 BW]<>BLANK()),[02 BW]) 
var _LastBWLevel= CALCULATE( RIGHT( MAX('bar-1 (2)'[Code82]),2),FILTER(ALL('bar-1 (2)'),[02 BW]=_LastBWDate )) 
return IF(MAX('bar-1 (2)'[Code82])="R"&FORMAT(_levelMinus3,"00") , IF(_levelMinus3> CONVERT(_LastBWLevel,INTEGER),"#FF5733")) 

There is "T01" in your data, so what logic do we need to handle levels that not starts with "R"?

Best Regards,
Eyelyn Qin

Hi ,

There is "T01" in your data, so what logic do we need to handle levels that not starts with "R"?

yes 

Hi sameergupta60 ,

So... Can we ignore the "T01" to just consider R01 to Rxx?  Does my latset post help you?

Best Regards,
Eyelyn Qin

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

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.

Hi sameergupta60 ,

Such error is caused by two strange value in your data:

Can we ignore both?

Try to create the columns in Power Query instead:

Text.Select([Levels],{"0".."9"})

if [Rank]<>null then Text.Start([Levels],1) else null

Don't forget to change the Rank type to Number:

Then try:

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

 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.