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

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 )

ragygad's avatar
ragygad
Frequent Visitor
3 years ago

its not working , please check the attachement 

the first 2 grade value should have been 0 since they are not higher than any other values in the same field

  • AbhinavJoshi's avatar
    AbhinavJoshi
    Responsive Resident
    3 years ago

    Sorry for the missight, try this 
    HigherGradeFlag =
    VAR CurrentID = Table1[ID]
    VAR CurrentGrade = Table1[Grade]
    VAR PreviousGrade =
    CALCULATE (
    MAX ( Table1[Grade] ),
    FILTER ( Table1, Table1[ID] = CurrentID && Table1[Grade] < CurrentGrade )
    )
    VAR IsFirstOccurrence =
    CALCULATE (
    COUNTROWS ( Table1 ),
    FILTER ( Table1, Table1[ID] = CurrentID && Table1[Grade] < CurrentGrade )
    ) = 0
    RETURN
    IF ( IsFirstOccurrence || CurrentGrade > PreviousGrade, 1, 0 )

  • ragygad's avatar
    ragygad
    Frequent Visitor
    3 years ago

    its erroring