Forum Discussion

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...

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

Frequent Visitor

3 years ago

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

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

Related content