Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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?
Solved! Go to Solution.
@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.
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:
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
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
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,
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.