cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Frequent Visitor

Max for each subset of records

Hi

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

Best Regards,

1 ACCEPTED SOLUTION
Super User

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.

3 REPLIES 3
Super User

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.

Resolver II

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

Frequent Visitor

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

Announcements

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors