How to do dynamic left anti join?

[Anonymous]

Hi, 

I want to perform dynamic left anti join in powerbi. I have an erollment table in which i have user's emails and courses in which users are enrolled in and i have second table called users in which i have data of all the users (such as email, name and personal details). Now i want to find the users which are not enrolled in a course and the value should change according to the course i select in a slicer.
So basically, if i select a course in slicer. the user table and enrollment table should perform left anti join and get the emails which are in the users table but are not in enrollment table for that particular course. 

Thanks 

[Anonymous]

Hi @Anonymous,

Did Ashish_Mathur 's suggestions help with your scenario? if that is the case, you can consider Kudo or accept his suggestions to help others who faced similar requirements.

If that also doesn't help, please share more detailed information to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[Ashish_Mathur]

Hi,

Please share some data to work.

[Anonymous]

Enrollments (there are many columns which are related to course level)

Email	Course Title	start date	status
layal@gmail.com	abc	9/12/21	in progress

users report (contains all the users)

email	reporting department	office	job title
layal@gmail.com	Audit	Sydney	Manager
layal2@gmail.com	IT	QLD	Partner

The logic is if an user is enrolled in any course, the email of that user will be there in enrollment report. If not, then the user is not enrolled. 

[Ashish_Mathur]

Hi,

So if you select abc in the slicer, then in the resultant table, you want the result to be layal2@gmail.com.  Am i correct?  Share some more rows of data in both tables.  Also, there should be a third table which should list down all courses.  Share that as well.

[Anonymous]

Hi, Thats correct. I don't have any other table its just the enrollment reports it mostly contains all the courses. 

Email	Course Title	Start Date	Status
layal@gmail.com	abc		Not started
123@gmail.com	abc	9/12/21	completed
layal2@gmail.com	xyz	10/1/21	completed
layal@gmail.com	xyz		Not started

Email	reporting department	office	Job title
layal@gmail.com	Audit	NSW	Manager
layal2@gmail.com	IT	QLD	Admin
123@gmail.com	IT	NSW	IT
234@gmail.com	Tax	VIC	tax

So if i select abc course in slicer I should see layal2@gmail.com and 234@gmail.com users who are not enrolled. 

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[amitchandak]

@Anonymous , Check for use opted for that course. But need a separate usee table for that

Enrolled = countrows(enrollment )

not enrolled =if(isblank(countrows(enrollment ) ) , User[user], blank() )

not enrolled count = countx(values(User[user]),  if(isblank(countrows(enrollment ) ) , User[user], blank() )  )

[Anonymous]

There's a separate table but your if statement is not working. 

[Anonymous]

Hi Amit, I also need a drill down report to show the users details. Will this approach work then?