Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Dynamic column selection for matrix visual

hi   i have the columns along with calculated measures in the matrix visual . currently everything is static and matrix visual as a scroll and it is becoming hard for the users to come up with the ...
Need Help
  • MFelix's avatar
    MFelix
    7 years ago

    Hi Anonymous ,

     

    To what I can understand you want to have an option for users to select columns that they see on the matrix table that is not an option you have on PBI by default however you can create a workaround.

     

    Create an unrelated table with all your Columns Names that you want to have as on off on your matrix.

     

    In my case I created a table named Selector:

    Columns selection

    Quantity
    Value

     

    Then add the following measures:

    Column selection = MAX(Selector[Columns selection])

Totals Columns = SWITCH([Column selection];"Quantity";[Quantity total];"Value";[Value total])

    On the second measure you should add all the related measure that you have for each column you want to show.

     

    Now create your matrix in the following way:

    Rows: Category (this are the columns that you want to have the details (keep the ones you have)

    Columns: Selector[Columns Selection]

    Values: [Totals Columns]

     

    Should give the result below:

     

    See attach PBIX.

     

    Regards,

    MFelix 

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hey MFelix ,

 

I tried that and unfortunately didn't work for me. I used the same formulas as you did, same field names, but I get a matrix like this.

 

MFelix's avatar
MFelix
Super User
5 years ago

Hi Anonymous ,

 

Are you abble to share a sample file?

 

You need to use a onedrive, google drive, we transfer or similar link.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi MFelix ,

     

    Attaching the PBIX file in the link. Thanks for your support! 🙂

     

    OneDrive Link 

    • MFelix's avatar
      MFelix
      Super User
      5 years ago

      Hi Anonymous ,

       

      The question here is the way you have made the Totals column measure syntax.

       

      You have:

      Totals Columns = SWITCH([Column selection];"Volume";[A_CM_Vol];"C3";[A_CM_C3])

      But your column selection table does not have any value that specifically is Volume. What the funcion switch is doing is:

      Totals Columns = IF([Column selection] = "Volume";[A_CM_Vol];IF([Column selection] = "C3";[A_CM_C3]))

      Instead of making the nested IFs you used the switch.

       

      You need to redo your measure to:

      Totals Columns = SWITCH([Column selection];"A_CM_Vol";[A_CM_Vol];"A_CM_C3";[A_CM_C3])

      Then you will get the correct values:

      Check PBIX file attach.

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Woah, i did not know that. Thanks a lot! Just one more query, Can i group all those into say some specific fields?

         

        As you can see in the attached image I have around 12 measures for Actual values, I have another 12 for Plan & Previous Year (Total 35). 

         

        For Eg: Right now I have a matrix view with 40 columns. Say i just want to see C3, C4 & Volume, instead of selecting, A_CM_C3, P_CM_C3 and other 4 measures, is there a way i can just have one field called C3, C4, Volume and it selects/deselects the respective fields automatically?