topic Re: Get Max of a column grouping by another columns in DAX Commands and Tips

Get Max of a column grouping by another columns

Anonymous — Mon, 14 Nov 2022 09:55:58 GMT

I have a table similar to this:

all what I want is to add a new measure to a table in power bi that returns the maximum "Value" grouped by "Project" and "Category" columns

Re: Get Max of a column grouping by another columns

Anonymous — Mon, 14 Nov 2022 10:13:29 GMT

Anonymous , please try this measure:

MaxValueByProjectAndCategory = CALCULATE( MAX('Table1'[Value]) ,ALL('Table1'[Date]) )

This yeilds these results:

Re: Get Max of a column grouping by another columns

Anonymous — Mon, 14 Nov 2022 10:51:31 GMT

Thanks EylesIT,
But the table above is just for example.
Say that I have a table of 20 columns and I want to get the max of one column grouping by another two columns ...it won't be practical to exclude all the other 17 columns .... is there another way?

Re: Get Max of a column grouping by another columns

Anonymous — Mon, 14 Nov 2022 11:21:57 GMT

Anonymous , Could you please post a sample data table with the 20 columns and what the expected output should be, please?

When you say "it won't be practical to exclude all the other 17 columns" do you mean that you want to display all 20 columns in the table visual, but you still want this measure to only group by the Product and Category per row? If that is the case, then try this version of the measure:

MaxValueByProjectAndCategory = VAR vProject = SELECTEDVALUE('Table1'[Project]) VAR vCategory = SELECTEDVALUE('Table1'[Category]) RETURN CALCULATE( MAX('Table1'[Value]) ,ALL('Table1') ,'Table1'[Project] = vProject ,'Table1'[Category] = vCategory )

Re: Get Max of a column grouping by another columns

FreemanZ — Mon, 14 Nov 2022 11:51:50 GMT

In such case, typically a table would be created with the code below:

MaxTable =

ADDCOLUMNS(

SUMMARIZE (Data, Data[Project], Data[Category]),

"MaxValue",

CALCULATE (MAX(Data[Value]))

)

With SUMMARIZE, we could include and summarize columns as many as needed. Columns from other table could also be included, as long as they are from a table on the one side of the relationship with Data table.

Re: Get Max of a column grouping by another columns

v-xiaosun-msft — Wed, 23 Nov 2022 02:02:21 GMT

Hi Anonymous

I suggest that you can use CALCULATE function and EARLIER function to create a calculated column, then you will get the expected result.

Sample data:

Create a column.

Column = CALCULATE ( MAX ( 'table1'[VALUE] ), FILTER ( 'table1', 'table1'[Project] = EARLIER ( 'table1'[Project] ) && 'table1'[Category] = EARLIER ( 'table1'[Category] ) ) )

Final output:

I attach my sample below for your reference.

Best Regards,
Community Support Team _ xiaosun

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