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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Filtering to show data that is not present in two sources

I've searched all over and don't seem to be able to nail this one down.

 

I've got two data sources, both .csv based.  One is a limited list of employees that have attended specific training courses.  The other is a larger list of all employees.  I have them linked via a matching employee ID column.

 

What I need to do, is build a visual that only shows employee IDs that do NOT exist in the smaller subset, but do exist in the larger source.

 

It seems simple but I can't seem to figure out how to make a filter that will exclude any IDs that show up in the smaller source.

 

Any ideas?

 

Thanks!

 

-A-

 

I feel like I might be missing something obvious.  Any ideas?

1 ACCEPTED SOLUTION
Anonymous
Not applicable

@Anonymous,

The easiest way I could think of is to do a left anti join from your larger list of employee to your smaller list of employees attended training using Merge Queries as New in Querry Editor as a separate table, and use this table only to build your visual. I believe there is another way to do this in DAX, but I am no expert, so let's see what DAX experts can come up with. 

View solution in original post

6 REPLIES 6
Anonymous
Not applicable

So thankful to you folks for helping me out with this!  I've bee using Power BI for quite some time but am just recently starting to dive deep.

 

The merge suggestion from @Anonymous actually works beautifully for what I want.  Never thought of merging the tables as until this moment I didn't know what the anti join functionality did!

 

Question though:  When I update the source files (the original two sources), will this update the merged table as well, or would I have to manually run that?

 

@Anonymous:  Thanks for going through the trouble of handing me a solution.  I opened it up and tried it out but for some reason I can't get the relationship between the two ID columns to run as a one to one relationship (it's currently a many to one).  Not sure why as it should contain the same subset.  Any idea why two columns with the same core data would be locked down to a many to one relationship?  It's possible I'm not understanding somethign fundamental here.

 

-A-

hi, @Anonymous

For leftanti join: A left anti join returns that all rows from the first table which do not have a match in the second table.

https://docs.microsoft.com/en-us/powerquery-m/joinkind-leftanti

For your Question though:

When you update the source files, you don't need to manually run it.

in power bi desktop, you just click Refresh button like this:

11.JPG

https://docs.microsoft.com/en-us/power-bi/refresh-desktop-file-local-drive

 

In power bi service, you need to install On-premises data gateway first and add the data source into it.

Then configuring scheduled refresh for it.

https://docs.microsoft.com/en-us/power-bi/refresh-data

https://docs.microsoft.com/en-us/power-bi/refresh-scheduled-refresh

 

 

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

If the relationship is many to one the only reason is that the values in your matching column (employee ID?) on the table at the many side are not unique
Anonymous
Not applicable

let me see, you have

PARTICIPANT table with employee A, B, D

ALLEMPLOYEES table with employees A, B, C, D

 

you've linked them directly with a 1:1 relationship?

 

What you could do is to go into the ALLEMPLOYEES table and add a column

NameInParticipant = RELATED(Participant[name])

the RELATED will lookup into the Participant table and find out the column Name value (replace "name" with basically any column you might have). If it does NOT find a value linked (because it won't be there) it will be empty.

 

So in the end EMPTY columns in this column in Employees will be your missing people

 

pbix  file here https://www.dropbox.com/s/o4x750gu47lneqq/part.pbix?dl=0

Anonymous
Not applicable

by the way, you can also create a function "IF RELATED is not blank" and write "Present" instead of writing the name
IsPresent = IF (RELATED(Participant[name])!=BLANK();"Present";"Absent") 

Anonymous
Not applicable

@Anonymous,

The easiest way I could think of is to do a left anti join from your larger list of employee to your smaller list of employees attended training using Merge Queries as New in Querry Editor as a separate table, and use this table only to build your visual. I believe there is another way to do this in DAX, but I am no expert, so let's see what DAX experts can come up with. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.