Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

How could implement selecting columns which shows in table dynamically?

Hi,

I have requirment as follow:

we have a table :

A1A2A3M1M2M3
      
      
      
      

we need to give to user possibility to select A1, A2,A3 to show in the table or not.

I found this link :https://community.powerbi.com/t5/Desktop/Select-columns-to-show-on-table-visual/m-p/606363/highlight/false#M288749

But in this link the attributes(A1,A2,A3 )were put rows, but I need them as column.

I appreciate any suggestion and help on this problem.

Thank you.

Regards,

Matin 

Need Help
  • AllisonKennedy's avatar
    AllisonKennedy
    6 years ago

    Anonymous  It is a bit unconventional but you can try to unpivot all the A and M columns, so that you are left with: 

    C, Attribute, Value

     

    Also create a disconnected table (no relationships to current data model) for the M values that you want user to select. I called this table Slicer.

    Slicer:

    Select
    M1
    M2
    M3

     

    Then create the matrix with C in Rows, Attribute in Columns, Value in Values.

     

    Finally, create this measure to filter the Attribute: 

     

    ShowColumns =
    Var SlicerSelection = VALUES(Slicer[Select])
    Var AlwaysShow = {("A1"), ("A2"), ("A3")}
    Var ShowValues = UNION(SlicerSelection, AlwaysShow)
    RETURN
    IF(SELECTEDVALUE('Table'[Attribute]) IN ShowValues, 1, 0)
     
    Put a filter on the visual for Attribute using Top N and use the ShowColumns measure as the By Value to filter for Top 1.

15 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thank you amitchandak , it is very useful, but still there is a problem.

      I need to make A1,A2,A3 selectable and always show M1 and M2 and M3 .
      But when I added M1,M2 and M3 to Matrix as columns , they don't add to matrix.
      do you have any suggestion to solve this problem?

      sorry maybe my questions are so basic, because I am beginners in Power BI

      Regards,

      Matin 

      • v-lionel-msft's avatar
        v-lionel-msft
        Community Support
        6 years ago

        Hi Anonymous ,

         

        What problem have you met? You just need to do like this.

        1. Unpivot the columns.

        2. Add the columns to the matrix.

         

        Best regards,
        Lionel Chen

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

         

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Thank you AllisonKennedy  for the solution .

    I follow the steps but the value of measure is always 0! and the solution does not work.
    here are my tables :

    and I created measure as follow:

    ShowColumns =
    Var SlicerSelection = VALUES(Slicer[Column1])
    Var AlwaysShow = {("A1"); ("A2"); ("A3")}
    Var ShowValues = UNION(SlicerSelection; AlwaysShow)
    RETURN
    IF(SELECTEDVALUE('Table'[Attribute]) IN ShowValues; 1; 0)
    do you have any Idea?
    I appreciate your help on this matter.
    Regards,
    Matin
    • AllisonKennedy's avatar
      AllisonKennedy
      Community Champion
      6 years ago
      How have you configured the matrix visual? The measure will only work if there is only 1 Attribute selected, so you need to have attribute in Columns in the Matrix, and put this measure as the visual level filter as I explained in my previous reply, as a 'top N' filter. Can you share image of your report visual page Visualizations and Filters Pane to see what fields you've put in the visual? Redact any confidential info from the visual.