Power BI- Power Query Vs DAX- Conditional Max with a set of columns

Problem Statment: A series of tests have been performed on individuals. And test status and their values are given in different columns. We would like to get the max/min value of tests for each individual where the test status is yes.

The Data:

 

Expected Output 

 

Solution Approach:

As I do not want to use the pivot and unpivot data options. I would build a table using the Test and Value columns. This means for each row will have a table, and we will filter that table and take a max of the value column.  

A new column in the Power query will do that 

let 
_tab1 = Table.Max( Table.SelectRows( Table.FromColumns({{[Test 1],[Test 2],[Test 3],[Test 4]}, {[Value 1],[Value 2],[Value 3],[Value 4]}}, {"Test", "Value"}), each [Test] ="Yes"),"Value"),
_tab2 = try Record.Field(_tab1, "Value") otherwise 0
in 
_tab2

Table.FromColumns has been used to build a table using the list, each list has similar kinds of values( Test or value ). Table.SelectRows is used to filter the data. Table.Max to get max. But as Max is a record Record.Field has been used to get the desired field 

The same approach is used in DAX 

Max2 = Maxx(Filter(Union(Row("Test", [Test 1], "Value",[Value 1]),
                Row("Test", [Test 2], "Value",[Value 2]),
                Row("Test", [Test 3], "Value",[Value 3]),
                Row("Test", [Test 4], "Value",[Value 4]) ), [Test] = "Yes"), [Value])+0

Row and union are used to create a table, filter to filter the data, and maxx to get the max value 

And the result looks like

 

Please find the attached file below

 

Find all my Medium blogs here

Click Here to access all my blogs and videos in a jiffy via an exclusive visual glossary using Power BI.
Please like, share, and comment on these. Your suggestions on improvement, challenges, and new topics will help me explore more.

You Can watch my Power BI Tutorial Series on My Channel, Subscribe, Like, and share

Master Power BI

Expertise Power BI

Power BI For Tableau User

DAX for SQL Users