{"id":1978,"date":"2023-03-03T10:13:33","date_gmt":"2023-03-03T10:13:33","guid":{"rendered":"https:\/\/monocroft.com\/?p=1978"},"modified":"2023-03-03T10:13:35","modified_gmt":"2023-03-03T10:13:35","slug":"how-to-find-the-max-value-of-multiple-columns-for-every-row","status":"publish","type":"post","link":"https:\/\/monocroft.com\/how-to-find-the-max-value-of-multiple-columns-for-every-row\/","title":{"rendered":"How to Find the Max Value of Multiple Columns for Every Row"},"content":{"rendered":"\n
In Power BI, to find the max value of multiple columns for every row in DAX, you can use the MAX <\/strong>function as well as the SUMMARIZE <\/strong>function.<\/p>\n\n\n\n The purpose of the SUMMARIZE <\/strong>function is to group a table by one or more columns and create a new table that contains the grouped data. <\/p>\n\n\n\n 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.<\/p>\n\n\n\n Let’s get started.<\/p>\n\n\n\n To start with, you will use the SUMMARIZE <\/strong>function to group your table by one or more columns. Then create a new table that contains the grouped data. <\/p>\n\n\n\n The Where:<\/p>\n\n\n\n For example, imagine you have a table titled Sample_Data<\/strong> with columns Country, Name, Date of Birth<\/strong>, and Gender.<\/strong> <\/p>\n\n\n\n To group the table by all four columns and create a new table with the grouped data, you can use the following DAX formula:<\/p>\n\n\n\n This will create a new table that contains all unique combinations of the values in columns Country, Name, Date of Birth<\/strong>, and Gender<\/strong>, and the number of occurrences of each combination in the original table.<\/p>\n\n\n\n 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:<\/p>\n\n\n\n Where:<\/p>\n\n\n\n Also, you can use the MAX function to find the maximum value of multiple columns by specifying multiple columns in the function. <\/p>\n\n\n\n For example, to find the maximum value of columns Country, Name, Date of Birth<\/strong>, and Gender<\/strong>, in the Sample_Data<\/strong> table, you would use the following DAX formula:<\/p>\n\n\n\n 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:<\/p>\n\n\n\n This formula will create a new table that contains the maximum value of each of the four columns for every row.<\/p>\n\n\n\n Finding the max value of multiple columns for every row in DAX requires the use of both the SUMMARIZE <\/a><\/strong>and MAX<\/strong> <\/a>functions.<\/p>\n\n\n\n The SUMMARIZE <\/strong>function is to group a table by one or more columns and creates a new table with the grouped data, while the MAX <\/strong>function is used to calculate the maximum value of the specified columns. <\/p>\n\n\n\n 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.<\/p>\n\n\n\n I hope you understood these steps. <\/p>\n\n\n\n Thanks for reading.<\/p>\n","protected":false},"excerpt":{"rendered":" In Power BI, to find the max value of multiple columns for every row in DAX, you can use the … <\/p>\nGetting started: How to Find the Max Value of Multiple Columns for Every Row<\/h2>\n\n\n\n
The SUMMARIZE Function<\/h2>\n\n\n\n
SUMMARIZE <\/code><\/strong>function syntax for this process is:<\/p>\n\n\n\n
SUMMARIZE([table], [group_by_column1], [group_by_column2], ...], [aggregate_column1], [aggregate_column2], ...])<\/code><\/strong><\/pre>\n\n\n\n
\n
table<\/strong> <\/code>is the name of the table you want to group<\/li>\n\n\n\n
[group_by_column1], [group_by_column2], ...<\/strong><\/code> are the columns by which you want to group the table<\/li>\n\n\n\n
[aggregate_column1], [aggregate_column2], ...<\/strong><\/code> are the columns for which you want to calculate aggregate values<\/li>\n<\/ul>\n\n\n\n
SUMMARIZE(Sample_Data, Sample_Data[Country], Sample_Data[Name], Sample_Data[Date of Birth], Sample_Data[Gender])<\/code><\/strong><\/pre>\n\n\n\n
The MAX Function <\/h2>\n\n\n\n
MAX([column])<\/strong><\/code><\/pre>\n\n\n\n
[column]<\/strong><\/code> 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<\/strong> in the Sample_Data<\/strong> table, you would use the following DAX formula:<\/p>\n\n\n\nMAX(Sample_Data [Country])<\/code><\/strong><\/pre>\n\n\n\n
MAX(Sample_Data, Sample_Data[Country], Sample_Data[Name], Sample_Data[Date of Birth], Sample_Data[Gender])<\/code><\/strong><\/pre>\n\n\n\n
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]))<\/code><\/strong><\/pre>\n\n\n\n
Conclusion: How to Find the Max Value of Multiple Columns for Every Row<\/h2>\n\n\n\n