Max of a measure for each ID

[Anonymous]

Hi,

I have a sales table as follows :

Customer ID	Date	Sale	Measure 1	Measure 2
1	10/01/2022	40	0	1
1	15/02/2022	50	1	1
1	12/05/2022	60	1	1
2	15/10/2022	20	0	1
2	14/11/2022	34	1	1
3	15/03/2022	23	0	0

I have created a measure 1 which detects if the customer is a returning customer and thus returns either 0 or 1.

Now I need to create another measure (Measure 2) which gets the max value for every customer as shown in the table above. 

I've tried DAX below, but it doesn't work right.

Measure 2 = MAXX(CALCULATETABLE(VALUES('Sales[CustomerID]), ALLSELECTED('Sales'[CustomerID])), [Measure 1])

It returns 

Customer ID	Date	Sale	Measure 1	Measure 2
1	10/01/2022	40	0	0
1	15/02/2022	50	1	1
1	12/05/2022	60	1	1
2	15/10/2022	20	0	0
2	14/11/2022	34	1	1
3	15/03/2022	23	0	0

Please help!

[Jihwan_Kim]

Hi,

I am not sure how your datamodel looks like but please try something like below whether it suits your requirement.

Expected measure 2: =
MAXX (
    CALCULATETABLE ( ALLSELECTED ( Sales ), VALUES ( Sales[Customer ID] ) ),
    [Measure 1]
)

[Anonymous]

Hi Jihwan,
With this im getting some blank values it should either be 0 ore 1,

but result is looking like:

Customer ID	Date	Sale	Measure 1	Measure 2
1	10/01/2022	40	0	0
1	15/02/2022	50	1	1
1	12/05/2022	60	1	1
2	15/10/2022	20	0	0
2	14/11/2022	34	1	1
3	15/03/2022	23	0
3	14/10/2022	34	1

@Jihwan_Kim 

[v-rzhou-msft · Accepted solution]

Hi @Anonymous ,

I think you can try ALLEXCEPT() function in your code.

Measure 1 = 
VAR _PREVIOUSDATE =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[Customer ID] ),
            'Table'[Date] < MAX ( 'Table'[Date] )
        )
    )
RETURN
    IF ( _PREVIOUSDATE = BLANK (), 0, 1 )

Measure 2 = 
MAXX(ALLEXCEPT('Table','Table'[Customer ID]),[Measure 1])

Result is as below.

Best Regards,
Rico Zhou

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

[Jihwan_Kim]

Hi,

Please share your sample pbix file's link (Onedrive, googledrive, dropbox, ... ) here, and then I can try to look into it to come up with a more accurate solution.

Thank you.