Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
Anonymous
Not applicable

Filtering OUT names from a separate table

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!

 

1 ACCEPTED SOLUTION
harshnathani
Community Champion
Community Champion

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!

View solution in original post

8 REPLIES 8
harshnathani
Community Champion
Community Champion

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
Helper II
Helper II

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
Not applicable

I will try this out, thanks!

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

Anonymous
Not applicable

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. 

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
Not applicable

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

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. 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.