cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
New Member

Show "Alternate Candidates" that excludes a single page filter and shows mutually exclusive members

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:

 

pbi example.PNG

 

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.

3 REPLIES 3
Highlighted
Super User IV
Super User IV

Re: Show "Alternate Candidates" that excludes a single page filter and shows mutually excl

It sounds like you have a clear idea of how you want to do this, so I'm not entirely sure what your question is. If you can provide more info on your data model so we can understand the problem better, we may be able to help.

 


______________


Has this post solved your problem? Please mark it as a 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.


I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 


excelwithallison.com

Highlighted
New Member

Re: Show "Alternate Candidates" that excludes a single page filter and shows mutually excl

@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'

 

Highlighted
Super User IV
Super User IV

Re: Show "Alternate Candidates" that excludes a single page filter and shows mutually excl

Because Table 1 isn't in t he data model, it's difficult to reference that in your Table 2 for your except part. As you said, creating an unrelated duplicate of Candidates could get you what you need, or alternatively you could have dimension tables for each of the sliceable dimensions (as unrelated tables/not connected to Candidates object) and build in your filtering functionality using Measures and placing a filter on the measure, something like;

Status = IF(SELECTEDVALUE(DimQualification[Qualification],0)=SELECTEDVALUE(Candidates[Qualification],1) && SELECTEDVALUE(DimRegion[Region],0)=SELECTEDVALUE(Candidates[Region],1), "Best Candidates","Alternate Candidates")

 


______________


Has this post solved your problem? Please mark it as a 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.


I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 


excelwithallison.com

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Find your favorite faces from the community presenting at the Power Platform Community Conference!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Upcoming Events

Community Summit North America – Join Online!

Join this community-driven Power Platform digital event for unbiased support and problem-solving.

Top Solution Authors