cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

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

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors