Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I made a Switch table to use with the Filter vis with this structure:
Category Table
Category | Index |
A | 1 |
B | 2 |
C | 3 |
D | 4 |
E | 5 |
F | 6 |
My dataset is structured in this way:
Profile Table
Name | A | B | C | D |
Gaufrid Cash | Lead | Lead | Expired 10/1/16 | |
Joachim Wendelin | Team | Lead | 5/18/2017 | |
Wealhmær Leif | Lead | Lead | ||
Goraidh Ayberk | Lead | Team | Expired 10/5/16 | 11/29/2007 |
Dene Jonathan | Team | Team | ||
Wolfe Grant | Team | 2/6/2016 | ||
Jake Steel | Team | 2/6/2016 | ||
Sean Cody | Team | 1/1/2012 | ||
William Higgins | Lead | Team | ||
Gabriel Tiberius | Team | Team | 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()) )
Solved! Go to Solution.
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
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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |