CALCULATE with an OR condition for multiple FILTER conditions across multiple tables

[AM_XRX]

I have a list of computer names joined to two tables. I want to write a calculate statement that tests if the computer name in the primary list matches to either of the joined tables and only count it once.

Something to the effect of =CALCULATE(DISTINCTCOUNT(Table1[Computer Name]), FILTER(Table2, Table2[Computer Name] <> BLANK()) OR FILTER(Table3, Table3[Computer Name] <> BLANK()))

That is a simplified example. For complexity reasons I cannot combine the two tables so that I have a single join. 

[AM_XRX · Accepted solution]

I ended up using an anti-join in Power Query so that Table3 did not contain any Computer Name values present in Table2, that way my measure could be =CALCULATE(DISTINCTCOUNT(Table1[Computer Name]), FILTER(Table2, Table2[Computer Name] <> BLANK()) + CALCULATE(DISTINCTCOUNT(Table1[Computer Name]), FILTER(Table3, Table3[Computer Name] <> BLANK())).

This workaround yeilded the correct answer but I am still curious if there is a way count unique matches across multiple table joins each with their own filter criteria.

[v-jiascu-msft]

Hi @AM_XRX,

If you want to count the computers, you could try to bring all the columns of "computer" together with this formula. Then it's easy to count the items.

Table =
DISTINCT ( UNION ( VALUES ( Table1[Computer] ), VALUES ( Table2[Computer] ) ) )

Or we could find out if one item exist in another table with this formula.

existinanother =
LOOKUPVALUE ( Table2[Computer], Table2[Computer], Table1[Computer] )

Best Regards!

Dale

[GilbertQ]

You could use the "||" instead of the OR and see if that works.