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.
I have 3 dummy variables, D1-D3. Where I calculate the number of Customers with the measure “No. measure”.
However, I want to create a variable “Category”, to calculate the No.measure, like shown below:
Is there a way to “undo” dummy variables in DAX? The data is stored in datawarehouse, so I can't transform the data in Query.
One of the way might be in Power query, not sure how is your table and other data but for the sample provided by you, it should work:
Sample data used:
D1 | D2 | D3 | No. measure |
0 | 1 | 0 | 11492 |
1 | 0 | 0 | 6699 |
1 | 1 | 0 | 3272 |
0 | 0 | 1 | 1324 |
0 | 1 | 1 | 353 |
1 | 0 | 1 | 1345 |
1 | 1 | 1 | 698 |
Just past above code in PQ and it should format the table in such way that then you can simply produce the result as below:
Hope it helps.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlDSUTIEYjBtaGJppBSrEw0XAWEzM0tLuCBMwtjIHKISpgosaWxkAheEqTY2NUYxEqLQxBTFSBA2s7RQio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [D1 = _t, D2 = _t, D3 = _t, #"No. measure" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"D1", Int64.Type}, {"D2", Int64.Type}, {"D3", Int64.Type}, {"No. measure", Int64.Type}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"No. measure"}, "Attribute", "Value"),
#"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "Category"}}),
#"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each ([Value] = 1)),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Value"})
in
#"Removed Columns"
@lbendlinI can't use the "data-tab". The data is stored in a datawarehouse, and D1-D3 is three different columns. Is it another way around?
No, it's all manual labor from here on. You have to create a measure that manually collects the data from all three columns.
That's what I feared to do. Can you help me with the code for the measure?
Since you won't be able to provide sample data for your scenario (data warehouse connection) I won't be able to assist further. I have no access to any such environment.
Covering 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 |
---|---|
44 | |
32 | |
30 | |
18 | |
17 |