Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Slicer Help

I have the following table:


I want to create something like below for each green, yellow, and red values 

Is is possible to create slicer like this in Power BI? 

Need Help
Show and Tell
Tutorial Requests
  • stretcharm's avatar
    stretcharm
    8 years ago

     

    Projects

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUXIvSk3NA9KRqTk5+eVARlBqCpA0MNQHIiMDQzOlWJ1oJScklRg6jIBqjUFqzcFqneGGoOswNNQ3NEAodMG0FkIaWkAtt1CKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Project = _t, Metric1 = _t, Metric2 = _t, Metric3 = _t, StartDate = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"StartDate", type date}})
in
    #"Changed Type"

    ProjectMetrics

     

    let
    Source = Projects,
    #"Unpivoted Columns1" = Table.UnpivotOtherColumns(Source, {"Project", "StartDate"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns1",{{"Attribute", "Metric"}, {"Value", "State"}})
in
    #"Renamed Columns"

     

    As your filtering the metric table you may need to add bidirectional filters, though this should be avoid if not need as it slows the model.

4 Replies

  • stretcharm's avatar
    stretcharm
    Memorable Member
    8 years ago

    Yes if you unpivot the data. You can then either have 2 slicers or a page level filter for each States.

     

    This is the advanced editor query editor text for a sample.

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUXIvSk3NA9KRqTk5+eVARlBqilKsTrSSE5IkmiKQtDNUKboikJwLunlQU2MB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Project = _t, Metric1 = _t, Metric2 = _t, Metric3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project", type text}, {"Metric1", type text}, {"Metric2", type text}, {"Metric3", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Project"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "Metric"}, {"Value", "State"}})
in
    #"Renamed Columns"

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Thank you for your quick reply. However, I made all calculation using calculated column within my data model. Is it still possible to unpivot it within the data model? Perhaps like making a copy and unpivot?

      • stretcharm's avatar
        stretcharm
        Memorable Member
        8 years ago

        Yes but you may need to have 2 queries one at project and one at project metric level then join these.