Dax help

[sameergupta60]

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.

please help what to do.

[Anonymous]

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.

[Anonymous]

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.

[sameergupta60]

Hi Evelyn,

thank for the solution we have gone & apply the same logic but in some projects, it is not applied.

please download the file below the link.

https://wetransfer.com/downloads/f51a4fa608980838b7b285172d0f033920220422091312/a6310f8b9730dc294c20...

Regards,

Sameer 

[sameergupta60]

Level Rank = CONVERT(RIGHT([Code82],2),INTEGER) while creating column getting error

Cannot convert value 'G' of type Text to type Integer.

[Anonymous]

Hi @sameergupta60 ,

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

Best Regards,
Eyelyn Qin

[sameergupta60]

yes, your last post helps us so much but we need logic from T01.

if it is possible please 

[sameergupta60]

Hi ,

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

yes 

[Anonymous]

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

[Anonymous]

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

[sameergupta60]

Hi,

please download the pbxi file from below link.

https://wetransfer.com/downloads/fb191b4f62992ccdd49b42944de1d25b20220412055105/58164592124d3ea0a0c7...

[Anonymous · Accepted solution]

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.

[sameergupta60]

Hi,

thanks for the solutions but one issue while applying on conditional formating on level & BW getting error.

a date column containing duplicate dates was specified in the call to function 'last date' this is not supported

[Anonymous]

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.

[sameergupta60]

hi,

thanks for the reply.

my RCC works on level r13,  [01 RCC] has date value when [Levels]="R13"? if my work is completed on level R13 or any other level then my BW should be below 3 levels but in current data, it is on R04 or r05 so we have to highlight that schedule should be on R10 level with color.

2.then BW at least on level  r10,   need a value? for [02 BW] when [Levels]=" R10"? we need to highlight the same on R10 of BW with color 

[sameergupta60]

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