Forum Discussion

ragygad's avatar
ragygad
Frequent Visitor
3 years ago

Compare values with if statement

I have a table with multiple fields and my main concern is 2 fields, one has ID and the other has grade (numerical field).  I am trying to check first id ID to ID match in the same field and if they...
Need Help
AbhinavJoshi's avatar
AbhinavJoshi
Responsive Resident
3 years ago

To achieve it in DAX, try this code NewColumn =
IF(
Table1[ID] = EARLIER(Table1[ID]),
IF(
Table1[Grade] > EARLIER(Table1[Grade]),
1,
0
),
BLANK()
)

ragygad's avatar
ragygad
Frequent Visitor
3 years ago

I tried that but you cant have earlier in a function with if statement , it only works with filter function 

 

  • AbhinavJoshi's avatar
    AbhinavJoshi
    Responsive Resident
    3 years ago

    Oh I see, can you try this 
    NewColumn =
    IF(
    Table1[ID] = CALCULATE(MAX(Table1[ID]), FILTER(ALL(Table1), EARLIER(Table1[ID]) = Table1[ID])),
    IF(
    Table1[Grade] > CALCULATE(MAX(Table1[Grade]), FILTER(ALL(Table1), EARLIER(Table1[ID]) = Table1[ID])),
    1,
    0
    ),
    BLANK()
    )

    • ragygad's avatar
      ragygad
      Frequent Visitor
      3 years ago

      it doesnt work. 
      so this is a dummy table of what i was trying to explain

       

       

      • AbhinavJoshi's avatar
        AbhinavJoshi
        Responsive Resident
        3 years ago

        Seeing the table helps, try this calculated column
        HigherGradeFlag =
        VAR CurrentID = Table1[ID]
        VAR CurrentGrade = Table1[Grade]
        VAR PreviousGrade =
        CALCULATE (
        MAX ( Table1[Grade] ),
        FILTER ( Table1, Table1[ID] = CurrentID && Table1[Grade] < CurrentGrade )
        )
        RETURN
        IF ( CurrentGrade > PreviousGrade, 1, 0 )