Forum Discussion

Proggesnekka_v2's avatar
Proggesnekka_v2
Regular Visitor
3 years ago

Multiple dummy variables to one column

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.

7 Replies

    • Proggesnekka_v2's avatar
      Proggesnekka_v2
      Regular Visitor
      3 years ago

      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's avatar
        lbendlin
        Icon for Super User rankSuper User
        3 years ago

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

  • mahenkj2's avatar
    mahenkj2
    Icon for Solution Sage rankSolution Sage
    3 years ago

    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's avatar
      mahenkj2
      Icon for Solution Sage rankSolution Sage
      3 years ago
      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"