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.⚡

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.⚡