Multiple dummy variables to one column

[Proggesnekka_v2]

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.

[mahenkj2]

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.

[mahenkj2]

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"

[lbendlin]

this seems to be vaguely related:

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

[Proggesnekka_v2]

@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?

[lbendlin]

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

[Proggesnekka_v2]

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

[lbendlin]

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.