Matrix: Filter Columns Shown

[HenryJS]

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?

[parry2k · Accepted solution]

@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]

@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]

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