Comparing the row values based on the conditional category for each group

[Anonymous]

Hi,

Here are the data and the expected result. I want to compare the values based on the "Rule", which the relationship for "Tier",  to get the expected result, "pass" or "fail" for each Level using dax.  How can I get this ?  Thanks

[Jihwan_Kim · Accepted solution]

New Table =

VAR steponetable =

SUMMARIZE (

data,

data[id],

'level'[level],

tier[Index],

data[tier],

data[value]

)

VAR steptwotable =

ADDCOLUMNS (

steponetable,

"@lowertiersmaxvalue",

CALCULATE (

VAR currentlevel =

MAX ( 'level'[level] )

VAR currenttier =

MAX ( tier[Index] )

RETURN

MAXX (

FILTER (

steponetable,

'level'[level] = currentlevel

&& tier[Index] < currenttier

),

data[value]

)

)

)

VAR stepfourtable =

ADDCOLUMNS (

steptwotable,

"@result column",

IF (

COUNTROWS (

FILTER (

steptwotable,

'level'[level] = EARLIER ( 'level'[level] )

&& data[value] < [@lowertiersmaxvalue]

)

) > 0,

"Fail",

"Pass"

)

)

RETURN

SUMMARIZE (

stepfourtable,

data[id],

'level'[level],

data[tier],

data[value],

[@result column]

)

https://www.dropbox.com/s/9dxtafp4guftm5c/jmywangv2.pbix?dl=0 

[Anonymous]

@Jihwan_Kim 

Very appreciated that. Let me figure it out all.  Thanks.

[Anonymous]

@amitchandak 

Can you explain the function you wrote?  Because I can't understand why you used the LEFT function to extract the first characters from [Position], the column, [Position], represents a set of group that it shouldn't contain any additional information in that equation.

To clarify my question, under the each of group ([Position]), I want to check whether the values, which is split into three levels, would follow the rule or not.  That's to say, we don't need to care about the difference of values in the same [position] and the same [tier].    Thank you.

[amitchandak]

@Anonymous , Based on what I got Try a new column as

new column =
if(search(left(level],1), [Rule],,0) <= search([tier] [Rule],,0), "Pass", "Fail")