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 : A1 A2 A3 M1 M2 M3                                                 we need to give to user possibility...
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.
AllisonKennedy's avatar
AllisonKennedy
Icon for Community Champion rankCommunity 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.
Anonymous's avatar
Anonymous
Not applicable
6 years ago

AllisonKennedy 

here is my Matrix configuration and Filter :

 

  • AllisonKennedy's avatar
    AllisonKennedy
    Icon for Community Champion rankCommunity Champion
    6 years ago
    Make sure the Top N is set to Top 1. Sorry I should have specified in original post. I have updated now.
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      AllisonKennedy , even with Top 1 does not work.

      here is the result, as you could see the measure returns 1 for A1,A2,A3 ( which I want to be selacteble), and 0 for M1, M2 and M3.

      and infact Slider does not doing anything .

      for sure I did something wrong but I could not find it 😞

      • AllisonKennedy's avatar
        AllisonKennedy
        Icon for Community Champion rankCommunity Champion
        6 years ago
        Sorry, I did it the other way around and made M1, M2, M3 selectable. Just update the measure to replace A1, A2, A3 with M1, M2, M3:

        ShowColumns =
        Var SlicerSelection = VALUES(Slicer[Column1])
        Var AlwaysShow = {("M1"); ("M2"); ("M3")}
        Var ShowValues = UNION(SlicerSelection; AlwaysShow)
        RETURN
        IF(SELECTEDVALUE('Table'[Attribute]) IN ShowValues; 1; 0)