Calculating a dynamic anti join table

[Adidas]

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?

[harshnathani · Accepted solution]

Hi @Adidas ,

I think this is what you want.

Pbix file is attached with the solution

https://community.powerbi.com/t5/Desktop/Count-of-Customers-with-no-Sales/td-p/589155

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[Adidas]

Thank you all for your kind help

it works fine now

[amitchandak]

@Adidas , you can use inside a measure, but costly stuff for measure

antijoin table =

countx(
EXCEPT (
SELECTCOLUMNS ( SalesPersons, "ID", SalesPersons[worker id] ),
SELECTCOLUMNS ( 'Salesfact', "ID", 'Salesfact'[worker id] )
) ,[worker id] )

[Adidas]

Thanks amitchandak

I created the measure as you suggested

measure =

countx(

EXCEPT (

SELECTCOLUMNS ( SalesPersons, "ID", SalesPersons[worker id] ),

SELECTCOLUMNS ( 'Salesfact', "ID", 'Salesfact'[worker id] )

) ,[ID] )

How can I show now the list of employees with no daily sales?

[Adidas]

Hi

forgot to mention I'm on a direct query mode
cant use the query editor here

I should perform it by pure dax

[harshnathani]

Hi @Adidas ,

See if these help you 

https://radacad.com/how-to-change-joining-types-in-power-bi-and-power-query#:~:text=In%20Power%20BI%...

https://curbal.com/blog/joining-table-in-power-bi-with-power-query-and-dax

https://www.tutorialgateway.org/joins-in-power-bi/

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)