Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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

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

 

 

 

 

Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    5 years ago

     

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

4 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    5 years ago

     

    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]
    )
     
     
     
     
    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Jihwan_Kim 

       

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

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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.