Column filtering with the result of another request

[stefb65]

Hello

I want to filter the content of a column with the result of another request. My need is the following one : I have two tables, one with our employees (ID, service, name and firstname) and another one with the training courses followed by the employeeq (ID, course and date). 

I'd like to get the employees that didn't follow any training with the ability of filtering on the year (how many in 2022, how many in 2023, etc.). In SQL language this is very easy to do : select ID, name, firstname from employees where id not in (select id from trainings where year(date)=2022)

I'm struggling on how to do this in PowerBi with Power Query and mybe some DAX functions. 

Do you have any idea on how this could be done ? 

Thanks in advance. Regards

Stéphane

[MarkLaf · Accepted solution]

Make sure you have a relationship defined between Employees and Courses table. Something like:

If you just want a visual of Employee columns where they have zero related courses, then the simplest (but not best practice) way would be to

1. Add an implicit count measure from your Courses table
2. set the filter in the filter pane of the implicit measure to 'is blank'
3. remove the implicit measure

Here is a quick gif showing these steps (note, I just added a Year column to my Courses table, but best practice would be to create a Date table):

If you mean that you need the DAX to craft a virtual table of Employees with zero related courses in any filter context (like, when Year = 2022), to then use further on in your measure/column, then you would use something like the below. This is an example measure where we count the rows of Employees with no related courses:

Missing Courses Count = 
CALCULATE(
    COUNTROWS( Employees ),
    // The below filter gets us rows of Employees where we can't find any related Courses rows
    FILTER( Employees, CALCULATE( ISEMPTY( Courses ) ) )
)

[Anonymous]

Hi @stefb65,

I wanted to follow up since we haven't heard back from you regarding our last response. We hope your issue has been resolved.
If the community member's answer your query, please mark it as "Accept as Solution" and select "Yes" if it was helpful.
If you need any further assistance, feel free to reach out.

Thank you,
Pavan.

[Anonymous]

Hi @stefb65,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please "Accept  as  Solution" and give a 'Kudos' so other members can easily find it.

Thank you,
Pavan.

[rohit1991]

Hi @stefb65 

You can handle this in Power BI using DAX by comparing all employees against those who appear in the training records for the selected year. One way is to use a measure with the EXCEPT function, for example:

COUNTROWS(EXCEPT(VALUES(Employees[ID]), VALUES(FILTER(Trainings, YEAR(Trainings[Date]) = SelectedYear)[ID])))

where SelectedYear comes from a slicer or parameter. Another option is to add a calculated column in the Employees table to check if an employee has training records for the chosen year using CALCULATE and FILTER. While Power Query could also do this, DAX is better for dynamic filtering with slicers, letting users easily pick a year and see which employees did not attend training.

[Anonymous]

Hi @stefb65,

Thank you for reaching out in Microsoft Community Forum.

Thank you @PwerQueryKees , @MarkLaf , @lbendlin    for the helpful response.

As suggested by PwerQueryyKees,MarkLaf, Ibendlin.,  I hope this information was helpful. Please let me know if you have any further questions or you'd like to discuss this further. If this answers your question, please "Accept as Solution" and give it a 'Kudos' so others can find it easily.

Please continue using Microsoft community forum.

Regards,
Pavan.

[PwerQueryKees · Accepted solution]

How would this work for you?
Employees:

Course Attendance with a custum column to get the year = Table.AddColumn(Attendance, "Attendance Year", each Date.Year([Date])):

Producing for all years, the Employees not attending any course:

Using this query:

let
    Source = Attendance,
    // First group all attending employees by year
    #"Grouped Rows" = Table.Group(Source, {"Attendance Year"}, {{"Year Attendance", each _, type table}}),
    // for each row do a right anti join on ID, with the employee table, getting the employees not attending in that year.
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Year Non Attendance", each Table.NestedJoin([Year Attendance],"ID",Employee,"ID","Not Attended",JoinKind.RightAnti)),
    // Get the joind Employees as a table
    #"Expanded Year Non Attendance" = Table.ExpandTableColumn(#"Added Custom", "Year Non Attendance", {"Not Attended"}, {"Not Attended"}),
    // Replace the result of the join with the actual ID's (expanding each row in multiple rows)
    #"Expanded Not Attended" = Table.ExpandTableColumn(#"Expanded Year Non Attendance", "Not Attended", {"ID"}, {"ID"}),
    // Remove helper columns
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Not Attended",{"Year Attendance"}),
    // Sort by Year and ID
    #"Sorted Rows" = Table.Sort(#"Removed Columns",{{"Attendance Year", Order.Ascending}, {"ID", Order.Ascending}})
in
    #"Sorted Rows"

Did I answer your question? Then please (also) mark my post as a solution and make it easier to find for others having a similar problem.
Remember: You can mark multiple answers as a solution...
If I helped you, please click on the Thumbs Up to give Kudos.

Kees Stolker

A big fan of Power Query and Excel

[MarkLaf · Accepted solution]

Make sure you have a relationship defined between Employees and Courses table. Something like:

If you just want a visual of Employee columns where they have zero related courses, then the simplest (but not best practice) way would be to

1. Add an implicit count measure from your Courses table
2. set the filter in the filter pane of the implicit measure to 'is blank'
3. remove the implicit measure

Here is a quick gif showing these steps (note, I just added a Year column to my Courses table, but best practice would be to create a Date table):

If you mean that you need the DAX to craft a virtual table of Employees with zero related courses in any filter context (like, when Year = 2022), to then use further on in your measure/column, then you would use something like the below. This is an example measure where we count the rows of Employees with no related courses:

Missing Courses Count = 
CALCULATE(
    COUNTROWS( Employees ),
    // The below filter gets us rows of Employees where we can't find any related Courses rows
    FILTER( Employees, CALCULATE( ISEMPTY( Courses ) ) )
)

[lbendlin]

I would recommend doing this in Power BI rather than in Power Query, and ideally have the data model do it for you.

If you like more help please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...