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.

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.

      • stretcharm's avatar
        stretcharm
        Memorable Member
        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.