Forum Discussion

SEwing's avatar
SEwing
Frequent Visitor
3 months ago
Solved

MAXX Returning duplicate values

I'm trying to create a column that returns the latest completion date for jobs which may have the same job number. This is the code: MaxDateFlag = VAR MaxDateForJob = MAXX ( FILTER ( 'Jobs', 'Job...
Need Help
  • Shai_Karmani's avatar
    Shai_Karmani
    3 months ago

    The duplicate happens because both rows share the same JobNumber AND the same JobDate, so the IF test passes for both. You need a tiebreaker so only one row wins.

    If your table has any unique row identifier (an Index column added in Power Query works well), use it as a secondary key:

    MaxDateFlag =
    VAR MaxDateForJob = MAXX ( FILTER ( 'Jobs', 'Jobs'[JobNumber] = EARLIER ( 'Jobs'[JobNumber] ) ), 'Jobs'[JobDate] )
    VAR MaxIndexForJob = MAXX ( FILTER ( 'Jobs', 'Jobs'[JobNumber] = EARLIER ( 'Jobs'[JobNumber] ) AND 'Jobs'[JobDate] = MaxDateForJob ), 'Jobs'[Index] )
    RETURN IF ( 'Jobs'[Index] = MaxIndexForJob, 'Jobs'[JobDate], BLANK () )

    If you do not already have an Index column, add one in Power Query (Add Column then Index Column from 0). The first MAXX gets the latest date per JobNumber, the second picks the row with the largest Index among the tied dates, and the IF flags only that one.

    If this helped, a thumbs up and accepting the solution would be appreciated.

    Best,
    Shai Karmani

Shai_Karmani's avatar
Shai_Karmani
Super User
3 months ago

The duplicate happens because both rows share the same JobNumber AND the same JobDate, so the IF test passes for both. You need a tiebreaker so only one row wins.

If your table has any unique row identifier (an Index column added in Power Query works well), use it as a secondary key:

MaxDateFlag =
VAR MaxDateForJob = MAXX ( FILTER ( 'Jobs', 'Jobs'[JobNumber] = EARLIER ( 'Jobs'[JobNumber] ) ), 'Jobs'[JobDate] )
VAR MaxIndexForJob = MAXX ( FILTER ( 'Jobs', 'Jobs'[JobNumber] = EARLIER ( 'Jobs'[JobNumber] ) AND 'Jobs'[JobDate] = MaxDateForJob ), 'Jobs'[Index] )
RETURN IF ( 'Jobs'[Index] = MaxIndexForJob, 'Jobs'[JobDate], BLANK () )

If you do not already have an Index column, add one in Power Query (Add Column then Index Column from 0). The first MAXX gets the latest date per JobNumber, the second picks the row with the largest Index among the tied dates, and the IF flags only that one.

If this helped, a thumbs up and accepting the solution would be appreciated.

Best,
Shai Karmani