Calculated column based on multiple column

[abdul26]

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. 

[edhans · Accepted solution]

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.

[tamerj1 · Accepted solution]

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.

[abdul26]

Thanks alot ! It helps a lot.  @edhans 

[edhans · Accepted solution]

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.