DAX formula to create a table which has max attempts for each user and returns responses

[arajan111]

Hi,

I have a Table imported via spreadsheet which has the following columns:

User ID	User Name	Attempt	Response 1	Response 2	Response 3	Response 4
123	Fish	1	Performing	Adopting	Performing	Exploring
123	Fish	2	Exploring	Exploring	Performing	Exploring
321	Chips	2	Explorin	Explorin	Explorin	Exploring
456	KFC	2	Exploring	Adopting	Performing	Exploring
456	KFC	3	Exploring	Performing	Performing	Performing

How can I use a DAX formula to give me the resultant table:

User ID	User Name	Attempt	Response 1	Response 2	Response 3	Response 4
123	Fish	2	Exploring	Exploring	Performing	Exploring
321	Chips	2	Explorin	Explorin	Explorin	Exploring
456	KFC	3	Exploring	Performing	Performing	Performing

[Musadev]

Hi @arajan111 
I have created the table, filtering the data by a calculated column.
Create a copy of the table and then add the below measure,  into the table.

Filter out the Rows where you are getting Max value in the new calculated column.  

Max Value =
VAR MaxResponse =
    CALCULATE (
        MAX ( 'tbl_Creation'[Attempt] ),
        ALLEXCEPT ( 'tbl_Creation', 'tbl_Creation'[User ID] )
    )
RETURN
    IF ( 'tbl_Creation'[Attempt] = MaxResponse, "Max", "" )

[Shreeram04 · Accepted solution]

Hi @arajan111 ,

Please create a calculated column that acts as a flag.

Refer below screenshot and Dax for your reference.

Max_Attempt_Flag =

VAR MaxAttempt = CALCULATE(MAX('Table'[Attempt]), ALLEXCEPT('Table', 'Table'[User ID]))

RETURN

IF('Table'[Attempt] = MaxAttempt, 1, 0)

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

Thanks,

Hari R