Forum Discussion

olimilo's avatar
olimilo
Icon for Post Prodigy rankPost Prodigy
9 years ago
Solved

Filter an unrelated table using a switch?

I made a Switch table to use with the Filter vis with this structure:

 

Category Table

CategoryIndex
A1
B2
C3
D4
E5
F6

 

My dataset is structured in this way:

Profile Table

NameABCD
Gaufrid CashLeadLeadExpired 10/1/16 
Joachim WendelinTeamLead 5/18/2017
Wealhmær LeifLeadLead  
Goraidh AyberkLeadTeamExpired 10/5/1611/29/2007
Dene JonathanTeamTeam  
Wolfe GrantTeam  2/6/2016
Jake Steel Team 2/6/2016
Sean Cody Team 1/1/2012
William HigginsLeadTeam  
Gabriel TiberiusTeamTeam 5/29/2009

 

Is it possible to filter the Profile table when the user selects a Category from the Category table? If the user selects Category A, the Profile table would only show people whose either Team/Lead for Category A.

 

I tried using the following measure but I'm getting The expression refers to multiple columns error.

 

Selection = 
SWITCH(
    TRUE(),
    VALUES('Category'[Category]) = "A", FILTER('Profile', 'Profile'[A] <> BLANK())
)
Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    9 years ago

    Hi olimilo,

     

    First, unpivot Profile Table structure under Query Editor mode. Select columns [A], [B], [C] and [D] at the same time and click the "Unpivot Column" option under Transform tab.

     

    You will get a new dataset structure.

    Return back to report view mode. Create a one to many relationship between Category table and Profile table based on common column [Category].

     

    Then, insert a Matrix visual and a Slicer. Add 'Category'[Category] as slicer item, drag relative fields from Profile table into Matrix.

     

    Best regards,
    Yuliana Gu

1 Reply

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    Hi olimilo,

     

    First, unpivot Profile Table structure under Query Editor mode. Select columns [A], [B], [C] and [D] at the same time and click the "Unpivot Column" option under Transform tab.

     

    You will get a new dataset structure.

    Return back to report view mode. Create a one to many relationship between Category table and Profile table based on common column [Category].

     

    Then, insert a Matrix visual and a Slicer. Add 'Category'[Category] as slicer item, drag relative fields from Profile table into Matrix.

     

    Best regards,
    Yuliana Gu