Distinct count across two tables with multiple critera

Anonymous — Wed, 03 Jan 2024 13:24:29 GMT

Hi!

I have two tables, Order Table and Item Table, both of which have the same primary key which is customer ID.

Both tables have a column which are similar but because of the way the database has been designed, they stand alone. Both have the column 'item colour'.

I need to a distinct count of the Customer ID's where they have both ordered a blue item colour but I'm struggling to write a measure which will look at both tables and filter both tables on the columns I need.

any help would be much appreciated!

Re: Distinct count across two tables with multiple critera

v-weiyan1-msft — Fri, 05 Jan 2024 08:39:54 GMT

Hi Anonymous ,

Based on your description,, Please try code as below.
My Sample:
Order Table:

Item Table:

Distinct Blue Customers = VAR _OrderTableBlue = CALCULATETABLE( VALUES('Order Table'[Customer ID]), FILTER( 'Order Table', 'Order Table'[Item Colour] = "Blue" ) ) VAR _ItemTableBlue = CALCULATETABLE( VALUES('Item Table'[Customer ID]), FILTER( 'Item Table', 'Item Table'[Item Colour] = "Blue" ) ) RETURN COUNTROWS( INTERSECT( _OrderTableBlue, _ItemTableBlue ) )

Result is as below.

If this reply still couldn't help resolve your issue, please provide detailed sample data and the results you are hoping for. We can better understand the problem and help you. Show it as a screenshot or excel. Please remove any sensitive data in advance.

Best Regards,
Yulia Yan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

topic Re: Distinct count across two tables with multiple critera in DAX Commands and Tips

Distinct count across two tables with multiple critera

Re: Distinct count across two tables with multiple critera