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', 'Jobs'[JobNumber] = EARLIER ( 'Jobs'[JobNumber] ) ), 'Jobs'[JobDate] )

RETURN IF ( 'Jobs'[JobDate] = MaxDateForJob, 'Jobs'[JobDate], BLANK () )

 

If there are multiple Job numbers, it returns the latest completion date only once (except below) and if one is blank it returns a blank - GREAT!

The problem is if a Job number has two completion dates that are the same it returns BOTH. I just need it to return one.

 

Any help would be greatly appreciated!

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

3 Replies

  • 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

  • MasonMA's avatar
    MasonMA
    Super User
    3 months ago

    Hi, you can try using an ID column and a second condition that only keeps one of those rows. 

    MaxDateFlag = 
VAR MaxDateForJob =
    CALCULATE (
        MAX ( 'Jobs'[JobDate] ),
        ALLEXCEPT ( 'Jobs', 'Jobs'[JobNumber] )
    )

VAR MinIDForMaxDate =
    CALCULATE (
        MIN ( 'Jobs'[ID] ),
        FILTER (
            ALLEXCEPT ( 'Jobs', 'Jobs'[JobNumber] ),
            'Jobs'[JobDate] = MaxDateForJob
        )
    )

RETURN
    IF (
        'Jobs'[JobDate] = MaxDateForJob
            && 'Jobs'[ID] = MinIDForMaxDate,
        'Jobs'[JobDate],
        BLANK ()
    )

     

     

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    3 months ago

    Hi,

    Please share some data to work with and show the expected result.  Share data in a format that can be pasted in an MS Excel file.