Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have a visual table that will be filtered by various slicers. This table shows the "Best Candidates" for a job, with slicers by qualifications and other demographics. For example, my best candidates should have a PhD (so I slice dimension Education Level to select "PhD"). This "best" table should only show candidates who match all the slicer selections. "Candidate Name" is on the table visual, and there are various DAX measures attached to this Candidate, like "Quality Score".
On the same page, using the same data entity (ideally, since I would still want to see "Quality Score" broken out by "Candidate Name"), I need a table visual of the "Alternate Candidates", which means:
1) Ignore 1 of the slicers (I will explicitly hard-code which one in the DAX). Currently, I have disabled the page interaction between the slicer and the new visual. For example, I might want to ignore Education Level when looking for an alternate candidate. So, I should see candidates with any Education Level in this "Alternate Candidates" table.
2) Not show Candidate Names that already appear in the "Best Candidates" table. Obviously, when you disable the Page Interaction between the Education Level slicer and the "Alternative Candidates" table visual, it will simply show all Education Levels, including your "Best" candidates who have a PhD, and anyone else who doesn't.
Example:
The main issue is that "Score" is a complex DAX measure that references several entities in the data model that have no relationships (by design), so duplicating the candidates table in the data model would be inconvenient as I would have to duplicate the measures on the new table.
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
@AllisonKennedy Ignore the data model for a minute, since it's just 1 object 'Candidates'. This object has all candidates in the universe, with various sliceable dimensions.
Basically, if I had to outline the analogous SQL syntax:
'Table 1' = basic table visual of the object, by candidate name, filtered by the page slicers; works as expected. USERS MUST select slicer values, so this table will always be a subset of the universe
'Table 2' = SELECT * FROM 'Candidates' EXCEPT SELECT * FROM 'Table 1'
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |