Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi @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.
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.
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
41 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
36 | |
33 | |
18 | |
18 |