Forum Discussion
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?
- Anonymous6 years ago
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)
6 Replies
- AnonymousNot applicable
Hi Adidas ,
See if these help you
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) - Adidas
Helper I
Hi
forgot to mention I'm on a direct query mode
cant use the query editor hereI should perform it by pure dax
- amitchandak
Super User
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
Helper I
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?
- AnonymousNot applicable
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
Helper I
Thank you all for your kind help
it works fine now