Forum Discussion

Adidas's avatar
Adidas
Icon for Helper I rankHelper I
6 years ago
Solved

Calculating a dynamic anti join table

I have two tables in my power bi model:
a. SalesFact table which contains sales per employee by dates and b.SalesEmployees which hold the salesperson's details
My goal is to show for each date a list of the salesperson with no sales on that date. (page has date slicer)
to calculate this "anti join" table I wrote this table function:
antijoin table =
EXCEPT (
SELECTCOLUMNS ( SalesPersons, "ID", SalesPersons[worker id] ),
SELECTCOLUMNS ( 'Salesfact', "ID", 'Salesfact'[worker id] )
)

The problem here is that the function does not take the user selection of date slicer into consideration
meaning it is calculating the anti join between the whole SalesFact table and SalesEmployees table regardless of the date chosen
how can I achieve my desired goal?

6 Replies