WPA Expiry Analysis getting MAX Function Error

[Anonymous]

For this employee, whose WPA expires on 09-08-2024, I need to search in columns WPA 1, WPA 3, WPA 4 (with a role filter for Floor Leader), WPA 5, WPA 9, WPA 10, WPA 11, WPA 12, WPA 14, and WPA 15 to retrieve the latest date and compare it with the WPA expiry date. If the date is before the WPA expiry date, it's marked as expired. If it's within 30 days of expiry, it's labeled as expiring soon. Only the entries in WPA 4 column corresponding to the Floor Leader role are considered. I'll implement this using DAX

Example Data :
Employee    Role      WP1              WP2         WP3          WP4        WP5           WP6           WPAEXPIRY   

Adam   TERMINAL  05-12-23    10-08-23 22-08-23                 10-08-23      10-08-23        09-08-24                                                                          FLOOR                                                      10-08-23

I Have created dax  for 

VAR LatestExpirationDate =

    MAXX (

        VALUES ( 'All'[Employee Name] ),

        MAXX (

            ROW (

                "WPA1 Latest Date", 'All'[WPA1 Latest Date],

                "WPA2 Latest Date", 'All'[WPA2 Latest Date],

                "WPA3 Latest Date", 'All'[WPA3 Latest Date],

                "WPA4 Latest Date", 'All'[WPA4 Latest Date- for Terminal],

                "WPA5 Latest Date", 'All'[WPA5 Latest Date],

                "WPA9 Latest Date", 'All'[WPA9 Latest Date],

                "WPA10 Latest Date", 'All'[WPA10 Latest Date],

                "WPA11 Latest Date", 'All'[WPA11 Latest Date],

                "WPA12 Latest Date", 'All'[WPA12 Latest Date],

                "WPA14 Latest Date", 'All'[WPA14 Latest Date],

                "WPA15 Latest Date", 'All'[WPA15 Latest Date]

            ),

            MAX (

                'All'[WPA1 Latest Date], 'All'[WPA2 Latest Date],

                'All'[WPA3 Latest Date], 'All'[WPA4 Latest Date- for Terminal],

                'All'[WPA5 Latest Date], 'All'[WPA9 Latest Date],

                'All'[WPA10 Latest Date], 'All'[WPA11 Latest Date],

                'All'[WPA12 Latest Date], 'All'[WPA14 Latest Date],

                'All'[WPA15 Latest Date]

            )

        )

    )

RETURN

    IF (

        LatestExpirationDate < MAXX(VALUES('All'[Employee Name]), MAX('All'[WPA EXPIRY])),

        IF (

            DATEDIFF ( LatestExpirationDate, MAXX(VALUES('All'[Employee Name]), MAX('All'[WPA EXPIRY])), DAY ) <= 30,

            "Expires Soon",

            "Expired"

        ),

        "Not Expired"

    )

but i am getting Error Too many arguments were passed to the MAX function. The maximum argument count for the function is 2.

Looking for support thanks in advance 😊

[Anonymous]

Hi @Anonymous 

Based on what you have provided, here are the only suggestions I can give you.

Please try to change the code to:

VAR LatestExpirationDate =
    MAXX (
        VALUES ( 'All'[Employee Name] ),
        MAXX (
            ADDCOLUMNS (
                VALUES ( 'All'[Employee Name] ),
                "WPA1 Latest Date", 'All'[WPA1 Latest Date],
                "WPA3 Latest Date", 'All'[WPA3 Latest Date],
                "WPA4 Latest Date", IF('All'[Role] = "FLOOR LEADER", 'All'[WPA4 Latest Date], BLANK()),
                "WPA5 Latest Date", 'All'[WPA5 Latest Date],
                "WPA9 Latest Date", 'All'[WPA9 Latest Date],
                "WPA10 Latest Date", 'All'[WPA10 Latest Date],
                "WPA11 Latest Date", 'All'[WPA11 Latest Date],
                "WPA12 Latest Date", 'All'[WPA12 Latest Date],
                "WPA14 Latest Date", 'All'[WPA14 Latest Date],
                "WPA15 Latest Date", 'All'[WPA15 Latest Date]
            ),
            [Value]
        )
    )
RETURN
    IF (
        LatestExpirationDate < MAXX(VALUES('All'[Employee Name]), 'All'[WPA EXPIRY]),
        IF (
            DATEDIFF ( LatestExpirationDate, MAXX(VALUES('All'[Employee Name]), 'All'[WPA EXPIRY]), DAY ) <= 30,
            "Expires Soon",
            "Expired"
        ),
        "Not Expired"
    )

If you can, please provide your pbix file and be careful to remove sensitive data.

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.