Forum Discussion

HenryJS's avatar
HenryJS
Post Prodigy
6 years ago
Solved

Matrix: Filter Columns Shown

Hi all,

 

I have placed the below three columns into matrix values - shown below.

 

Is there any possible way of having a drop down filter which enables you to choose which column is shown?

 

 

Data Protection
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • parry2k's avatar
    parry2k
    6 years ago

    HenryJS yes can be done using by adding a new table with 3 options for selection and use that table for the slicer and then add the following measure

     

    Selected Measure = 
VAR __selected = SELECTEDVALUE ( SlicerTable[Slicer] )
RETURN
SWITCH ( __selected,
  "Approved", SUM ( Table[Approved] ),
  "Missing", SUM ( Table[Missed] ),
  "Forecast", SUM ( Table[Frecast] )
)

     

    use above measure in your visuals

     

    I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

    ⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

3 Replies

  • parry2k's avatar
    parry2k
    Super User
    6 years ago

    HenryJS yes can be done using by adding a new table with 3 options for selection and use that table for the slicer and then add the following measure

     

    Selected Measure = 
VAR __selected = SELECTEDVALUE ( SlicerTable[Slicer] )
RETURN
SWITCH ( __selected,
  "Approved", SUM ( Table[Approved] ),
  "Missing", SUM ( Table[Missed] ),
  "Forecast", SUM ( Table[Frecast] )
)

     

    use above measure in your visuals

     

    I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

    ⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

    • HenryJS's avatar
      HenryJS
      Post Prodigy
      6 years ago

      parry2k Please could you share some more information on adding the 'slicer table'?

       

      I don't want to duplicate the table I have as it is a large dataset. Can I do this just by adding one measure?

       

      Thanks

      • parry2k's avatar
        parry2k
        Super User
        6 years ago

        HenryJS yes it will be one measure as posted in my original reply and for slicer table, add a table in power bi with 3 options, you are not duplicating any table, or you can paste this code below, by adding a blank query and then click advanced editor

         

        let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WciwoKMovS01RitWJVvLNLC7OzEsHs93yi1KTE4tLlGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Slicer = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Slicer", type text}})
in
    #"Changed Type"

         

        I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

        ⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡