Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

How do i create a filter on feature?

Here is my underlying table

 

ProductFeature_1Feature_2Feature_3Feature_4
A1100
B1010
C1111
D0001
E1001

 

I would like to create a filter drop_down when i

 

select Feature_1, it will return me A, B, C, E

select Feature 2, it wil return me A, C

 

Is there a way to do this in PowerBI?

  • Hi Anonymous , 
    Yes you can do this in PowerBI .
    Go to Power Query and select all features column from 1 to 4 and unpivot it. 

     

    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUtJBQrE60UoBRfkppcklQK5bamJJaVFqvCES2wiJbYzENgHrdQSKGEKxARiDRJ2QRAzhos5IaiEYJOoC1wlTDxJ1RTETLBoLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}}),
        #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
        #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"", type text}, {"_1", type text}, {"_2", type text}, {"_3", type text}, {"_4", type text}}),
        #"Promoted Headers1" = Table.PromoteHeaders(#"Changed Type1", [PromoteAllScalars=true]),
        #"Changed Type2" = Table.TransformColumnTypes(#"Promoted Headers1",{{"Product", type text}, {"Feature_1", Int64.Type}, {"Feature_2", Int64.Type}, {"Feature_3", Int64.Type}, {"Feature_4", Int64.Type}}),
        #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type2", {"Product"}, "Attribute", "Value"),
        #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "Feature"}})
    in
        #"Renamed Columns"

     

    Then plot silcer of feature column and table of product column . 
    ADD Visual level filter in table where value = 1 

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
     

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable

    Wholly **bleep**. This thing is insane

  • manvishah17's avatar
    manvishah17
    Solution Supplier

    Hi Anonymous , 
    Yes you can do this in PowerBI .
    Go to Power Query and select all features column from 1 to 4 and unpivot it. 

     

    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUtJBQrE60UoBRfkppcklQK5bamJJaVFqvCES2wiJbYzENgHrdQSKGEKxARiDRJ2QRAzhos5IaiEYJOoC1wlTDxJ1RTETLBoLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}}),
        #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
        #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"", type text}, {"_1", type text}, {"_2", type text}, {"_3", type text}, {"_4", type text}}),
        #"Promoted Headers1" = Table.PromoteHeaders(#"Changed Type1", [PromoteAllScalars=true]),
        #"Changed Type2" = Table.TransformColumnTypes(#"Promoted Headers1",{{"Product", type text}, {"Feature_1", Int64.Type}, {"Feature_2", Int64.Type}, {"Feature_3", Int64.Type}, {"Feature_4", Int64.Type}}),
        #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type2", {"Product"}, "Attribute", "Value"),
        #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "Feature"}})
    in
        #"Renamed Columns"

     

    Then plot silcer of feature column and table of product column . 
    ADD Visual level filter in table where value = 1 

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
     

    • Anonymous's avatar
      Anonymous
      Not applicable

      Actually this might not solved the problem? Because if I selected like feature 2 and 3. Is it returning multiple row of A? If i have any aggregration of the value. For example the below. Will it still be aggregrating everything correctly? Thanks

       

      ProductFeature_1Feature_2Feature_3Feature_4Sales
      A110015
      B101015
      C111123
      D000150
      E1001 
  • Anonymous's avatar
    Anonymous
    Not applicable

    How do i go to power query?Like in the data modeling?

  • Anonymous's avatar
    Anonymous
    Not applicable

    Sorry - my question is how do i replace the parameter with my table. Thanks. 

    • manvishah17's avatar
      manvishah17
      Solution Supplier

      hi Anonymous , can you please ellaborate more ?

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable

        I just don't know how do i replace the parameters in the powerQuery . For example this is how i would replace in the first line but i don't know how do i replace the rest.