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.

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.