topic Re: Max for each subset of records in DAX Commands and Tips

Max for each subset of records

beno — Fri, 05 Aug 2016 15:03:13 GMT

How could I obtain the max value for each product group in the table? This is what I have imaged to have:

New measure: Maxno = CALCULATE(MAX(pri[pri_no]);allselected(pri[ProductGroup]))

This is a subset of the data:

Product-Group Pri-No

A 1

A 2

A 3

B 1

B 2

C 1

D 1

D 2

D 3

D 4

This is the final wanted result:

A 3

B 2

C 1

D 4

Thanks for your comments.

Best Regards,

Re: Max for each subset of records

kaushikd — Tue, 07 Mar 2017 12:59:13 GMT

MAX() Function is used to get the maximum possible value of any field,

Now just you have to do is Create a measure

MaxNo = MAX(Pri[Pri-No])

Now In the Report view drop a table and into value select the field:-

Product-Group

MaxNo

The table will show the maximum Pri-No for each Product-Group

Re: Max for each subset of records

tabbey — Thu, 24 May 2018 04:07:44 GMT

Based on the example, how to take it to another level of MAX.

This is there result from the MaxNo = MAX(Pri[Pri-No])

A 3

B 2

C 1

D 4

How to get the MAX from this group?

Desire output:

Group MaxOfPri Max OfGroup

A 3 4

B 2 4

C 1 4

D 4 4

Re: Max for each subset of records

technolog — Mon, 28 Aug 2023 20:47:26 GMT

To achieve the desired result where you want to get the maximum value for each product group, you can use a combination of DAX functions. However, the measure you provided:

Maxno = CALCULATE(MAX(pri[pri_no]); allselected(pri[ProductGroup]))
will always give the maximum pri_no across all the selected product groups, rather than for each individual group.

To get the maximum value for each product group, you can simply use a calculated column, because a measure will always aggregate based on the context provided in a visual. In this scenario, you want to create a column that contains the maximum for each row based on the group it's in.

Here's how you can do it:

Using Calculated Columns:
Maxno = CALCULATE(MAX(pri[pri_no]), FILTER(pri, pri[Product-Group] = EARLIER(pri[Product-Group])))
This formula will create a new column in your table that, for each row, calculates the maximum pri_no value for that product group.

When you create a visualization, you would simply use Product-Group as your axis and then use the Maxno column as the value. Since all the values in a group are the same, it will show the correct max for each group.
If you decide later that you only want a table with unique product groups and their maximum pri_no, you can remove duplicates from the table visualization in Power BI or use the SUMMARIZE function to create a new table:

SummaryTable = SUMMARIZE(pri, pri[Product-Group], "MaxNo", MAX(pri[pri_no]))
This will give you a table with one row for each Product-Group and the corresponding maximum pri_no.