topic Re: Calculating a dynamic anti join table in DAX Commands and Tips

Calculating a dynamic anti join table

Adidas — Wed, 29 Jul 2020 10:33:23 GMT

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?

Re: Calculating a dynamic anti join table

Anonymous — Wed, 29 Jul 2020 10:49:50 GMT

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%20Desktop%20you,Right%20part%20of%20the%20join).

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)

Re: Calculating a dynamic anti join table

Adidas — Wed, 29 Jul 2020 10:55:00 GMT

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

I should perform it by pure dax

Re: Calculating a dynamic anti join table

amitchandak — Wed, 29 Jul 2020 11:14:17 GMT

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] )

Re: Calculating a dynamic anti join table

Anonymous — Wed, 29 Jul 2020 11:38:28 GMT

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)

Re: Calculating a dynamic anti join table

Adidas — Wed, 29 Jul 2020 11:38:29 GMT

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?

Re: Calculating a dynamic anti join table

Adidas — Wed, 29 Jul 2020 12:03:24 GMT

Thank you all for your kind help

it works fine now