Filtering OUT names from a separate table

[Anonymous]

I have a master list that has all users listed by their ID.  Then i have specific groups that we ran tests against and collected information on for those groups.  I know i can use the filter, and filter the page to only those users, but what i need to do is the reverse.  Capture data on all users but not include the ones in the specific groups.

Example:

Master List - all have a passing rate of 92%

I need passing rate on just the specific group of users

Group 1 - passing rate on only this group ( i used page filter and basic filtering to select only those folks)

Master list minus those in group 1 - need passing rate for these but exclude people in group 1.

Any help or guidance, this is a bit complex for me as a beginner.

Thank you!

[harshnathani · Accepted solution]

Hi @Anonymous ,

You can use EXCEPT function to do this.

https://www.youtube.com/watch?v=TL81opk59aE

https://dax.guide/except/

https://community.powerbi.com/t5/Desktop/How-to-display-Intersect-and-except-data-of-two-tables/td-p/159534

Regards,
Harsh Nathani

Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!

[danielkrol]

I think there are a couple of ways to accomplish this. I think I would do the next:

1. create a distinct table of the user ID's
2. Do not create a relationship between those tables. It should be disconnected.
3. Place this new table as a slicer on your screen
4. Create a measure like ("User" is your new distinct table)

select = IF(SELECTEDVALUE(Master[UID]) IN FILTERS('User'[ID]), 0,1)​

• Add this measure to the filterpane of visual you want to filter
• Tell in filter pane only to show if "select" = 1

It's a few steps, but I've used this technique multiple times and people seem to love this interaction.

[Anonymous]

I will try this out, thanks!

[danielkrol]

If you don't get it to work, you might want to post your PBIX file.

[Anonymous]

Yep, not working.  I can get the measure created, i disconnected the group list from the master table.  I think the complexity of another table where the activity is held may be causing issues.  When i add the measure to the filter on the card i have for "passed" % calculation.  I had to add a slicer for date - month/year to pull out data on specific time frame.

Reality - i have the following tables:

Activity table and when the activity occurred

Master table - provides link to user detailed information (most measures are created under this table

Then each table that now has a different group list to find understand that groups data.

I would need to scrub my pbix file, as it contains sensitive info.  All tables are have the same userID as the main key for linking.

I also was able to create a slicer with the group data that is no longer linked, but it doesn't impact the % passed data, and when adding the select measure to the visual filter, it doesn't allow me to update to state 1. 

[danielkrol]

It's hard for me to completely oversee, since the model is not known.

The disadvantage of filtering out this way is that it is all done by coding DAX, so you also need to change your other measures.

There are also techniques that use parameters to determine which rows to load into the datamodel. A change in a parameter causes the data to be loaded again, but then you can constrain the data by the value of the parameter. Never used it, and I don't know if it can help out, since loading data can take up quite an amount of time.

[Anonymous]

So i'm thinking, is there a way to build a new table from my master table but not include the group?

[danielkrol]

In Power Query you can create a copie of your table and the remove the column without the group. But I'm not sure if that is what you need though.