topic Re: Max of a measure for each ID in DAX Commands and Tips

Max of a measure for each ID

Anonymous — Thu, 08 Dec 2022 07:00:33 GMT

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!

Re: Max of a measure for each ID

Jihwan_Kim — Thu, 08 Dec 2022 07:42:18 GMT

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] )

Re: Max of a measure for each ID

Anonymous — Fri, 09 Dec 2022 01:58:15 GMT

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

Re: Max of a measure for each ID

Jihwan_Kim — Fri, 09 Dec 2022 03:51:11 GMT

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.

Re: Max of a measure for each ID

Anonymous — Fri, 09 Dec 2022 06:09:36 GMT

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.