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 

MFelix's avatar
MFelix
Super User
5 years ago

Hi Anonymous ,

 

The idea is the same because in this one we exchange the column values by measures:

 

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

 As you can see in the syntax above the [Quantity total] not having a name before it means its a measure, so in your case you need to create a disconnected table with your measures names and then adjust the formula above with the measure names, be aware that if the measure have different formats you must use the format, to make the correct formatting.

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.