Home » How to Find the Max Value of Multiple Columns for Every Row

How to Find the Max Value of Multiple Columns for Every Row

how to find the Max value of multiple columns for every row - monocroft

In Power BI, to find the max value of multiple columns for every row in DAX, you can use the MAX function as well as the SUMMARIZE function.

The purpose of the SUMMARIZE function is to group a table by one or more columns and create a new table that contains the grouped data.

In this article, I will explain how to group a table by one or more columns, find the max value of the columns, and create a new table with the grouped and aggregated data.

Let’s get started.

Table of Contents show

Getting started: How to Find the Max Value of Multiple Columns for Every Row

To start with, you will use the SUMMARIZE function to group your table by one or more columns. Then create a new table that contains the grouped data.

The SUMMARIZE Function

The SUMMARIZE function syntax for this process is:

SUMMARIZE([table], [group_by_column1], [group_by_column2], ...], [aggregate_column1], [aggregate_column2], ...])

Where:

  • table is the name of the table you want to group
  • [group_by_column1], [group_by_column2], ... are the columns by which you want to group the table
  • [aggregate_column1], [aggregate_column2], ... are the columns for which you want to calculate aggregate values

For example, imagine you have a table titled Sample_Data with columns Country, Name, Date of Birth, and Gender.

To group the table by all four columns and create a new table with the grouped data, you can use the following DAX formula:

SUMMARIZE(Sample_Data, Sample_Data[Country], Sample_Data[Name], Sample_Data[Date of Birth], Sample_Data[Gender])

This will create a new table that contains all unique combinations of the values in columns Country, Name, Date of Birth, and Gender, and the number of occurrences of each combination in the original table.

The MAX Function

The next thing to do is to use the MAX function to calculate the max value of the specified columns. The MAX function has the following syntax:

MAX([column])

Where:

[column] is the name of the column for which you want to find the max value. For example,
to find the max value of column Country in the Sample_Data table, you would use the following DAX formula:

MAX(Sample_Data [Country])

Also, you can use the MAX function to find the maximum value of multiple columns by specifying multiple columns in the function.

For example, to find the maximum value of columns Country, Name, Date of Birth, and Gender, in the Sample_Data table, you would use the following DAX formula:

MAX(Sample_Data, Sample_Data[Country], Sample_Data[Name], Sample_Data[Date of Birth], Sample_Data[Gender])

After this, you can now include these MAX functions in the SUMMARIZE formula to find the max value of each column as a new column in the summary table. For example:

SUMMARIZE(Sample_Data, Sample_Data[Country], Sample_Data[Name], ample_Data[Date of Birth], Sample_Data[Gender], "Max", MAXSample_Data[Country], MAX(Sample_Data[Name]), MAX(Sample_Data[Date of Birth]), MAX(Sample_Data[Gender]))

This formula will create a new table that contains the maximum value of each of the four columns for every row.

Conclusion: How to Find the Max Value of Multiple Columns for Every Row

Finding the max value of multiple columns for every row in DAX requires the use of both the SUMMARIZE and MAX functions.

The SUMMARIZE function is to group a table by one or more columns and creates a new table with the grouped data, while the MAX function is used to calculate the maximum value of the specified columns.

Thus, by combining these two functions you can create a new table that contains the max value of each of the specified columns for every row.

I hope you understood these steps.

Thanks for reading.

People Also Read: