Distinct count across two tables with multiple critera

[Anonymous]

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!

[v-weiyan1-msft · Accepted solution]

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.