Power BI- Power Query Vs DAX- Conditional Max with a set of columns
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
