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 

wlad's avatar
wlad
Frequent Visitor
4 years ago

In my model I have 12 columns. Is there a way to sum the selected columns?

MFelix's avatar
MFelix
Super User
4 years ago

Hi wlad 

How are you selecting the columns?

  • wlad's avatar
    wlad
    Frequent Visitor
    4 years ago

    Hi,

    However I select with a slicer or directy in a matrix the sum is wrong

    Sum is correct only if I select one column

    • MFelix's avatar
      MFelix
      Super User
      4 years ago

      Hi wlad,

       

      How do you have everything setup? is it diferrent columns? If they are different columns the totals on the matrix will not sum the columns since each field is unique.

       

      For this you have two alternatives, create an unrelated table with the name of the columns you want to use and then adding a switch statement you can calculate each value individually and sum the total of all of them in another total value. Unpivo your columns and use the value for the columns on your matrix.

       

      Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

      If the information is sensitive please share it trough private message.

      • wlad's avatar
        wlad
        Frequent Visitor
        4 years ago

        Hi MFelix ,

        My data model is similar to "Column Selection Test.pbix " I found in an earlier post and since the table is built using summarizecollumns I can't unpivot it.

        So the second solution is suitable for me:
        "Create an unrelated table with the name of the columns you want to use and then adding a switch statement you can calculate each value individually and sum the total of all of them in another total value"

         

        Thank you for your help