topic Re: Multiple dummy variables to one column in DAX Commands and Tips

Multiple dummy variables to one column

Proggesnekka_v2 — Mon, 05 Jun 2023 12:05:43 GMT

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.

Re: Multiple dummy variables to one column

lbendlin — Wed, 07 Jun 2023 02:09:45 GMT

this seems to be vaguely related:

PIVOT your table in DAX | Data Awareness Programme (dataap.org)

Re: Multiple dummy variables to one column

Proggesnekka_v2 — Wed, 14 Jun 2023 11:48:58 GMT

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?

Re: Multiple dummy variables to one column

lbendlin — Wed, 14 Jun 2023 12:07:02 GMT

No, it's all manual labor from here on. You have to create a measure that manually collects the data from all three columns.

Re: Multiple dummy variables to one column

Proggesnekka_v2 — Wed, 14 Jun 2023 12:29:27 GMT

That's what I feared to do. Can you help me with the code for the measure?

Re: Multiple dummy variables to one column

lbendlin — Wed, 14 Jun 2023 12:36:40 GMT

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.

Re: Multiple dummy variables to one column

mahenkj2 — Wed, 14 Jun 2023 12:55:11 GMT

Hi Proggesnekka_v2

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.

Re: Multiple dummy variables to one column

mahenkj2 — Wed, 14 Jun 2023 12:55:50 GMT

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"