Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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.
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.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.