topic Calculated column based on multiple column in DAX Commands and Tips

Calculated column based on multiple column

abdul26 — Mon, 27 Mar 2023 15:44:19 GMT

Hello All,

I could use some help regarding a DAX calculation.

I have multiple columns upto 100 which has value 'x' or null.

In case if the column contains 'x' value, I need to capture the name of the column in the calculated column.

It would have been simple if each row only had one column value, but for a row , multiple columns can have value 'x'.

In that case I need to capture all the columns which have value 'x'. (Eg: id 2 has values in col2 and col4)

I have attached a screenshot of my table.

Re: Calculated column based on multiple column

edhans — Mon, 27 Mar 2023 16:33:00 GMT

I am not aware of a way to do this in DAX. The DAX functions cannot return columns and other metadata like that. Power Query can though.

let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUaoAYiiK1YlWMoKwK2AUSMwYrqQCpswERRlYyBRhEFhjLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Col1 = _t, Col2 = _t, Col3 = _t, Col4 = _t]), #"Replaced Value" = Table.ReplaceValue(Source,"",null,Replacer.ReplaceValue,{"Col1", "Col2", "Col3", "Col4"}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Replaced Value", {"ID"}, "Attribute", "Value"), #"Grouped Rows" = Table.Group(#"Unpivoted Other Columns", {"ID"}, {{"All Rows", each _, type table [ID=number, Attribute=text, Value=text]}}), #"Added Has X" = Table.AddColumn(#"Grouped Rows", "Has X", each Text.Combine([All Rows][Attribute], ", ")), #"Merged Queries" = Table.NestedJoin(#"Replaced Value", {"ID"}, #"Added Has X", {"ID"}, "Added Has X", JoinKind.LeftOuter), #"Expanded Added Has X" = Table.ExpandTableColumn(#"Merged Queries", "Added Has X", {"Has X"}, {"Has X"}) in #"Expanded Added Has X"

I did this by unpivoting data, then grouping it, then getting the colums that had an X.

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.

Re: Calculated column based on multiple column

abdul26 — Mon, 27 Mar 2023 17:31:47 GMT

Thanks alot ! It helps a lot. edhans

Re: Calculated column based on multiple column

tamerj1 — Tue, 28 Mar 2023 10:41:51 GMT

Hi abdul26
For you and for other searchers who are looking for a DAX measure solution that does not require typing the names of the existing 100 columns, can refer to attached sample file with the solution.