DAX query to compare a value in one table to see if it exists in another table

[nsy999]

This may well have been asked before! I have one table (01) with 60K of model numbers (devices from a CMDB) with a FIELD called MODEL. I have a second table (02) with model numbers FIELD that meet a certain criteria (EPEAT Rating). I want a result that gives me a calculation of how many models in Table 01 meet the criteria in Table 2. For clarity (or confusion), there may be 60k of listed devices in Table 01 (but say only 300 dispatre devices); in Table 02 there are only 100 disparte devices ( some devices in Table 01 may not be in Table 2 - which is fine). 

The simple answer example, that I am after is as simple as, Devices Meeting Criteria = 58k, Devices NOT Meeting Criteria = 2k.

The overarching purpose is to find out how many devices we have that either meet or fail to meet an EPEAT Rating http://greenelectronicscouncil.org/epeat/epeat-overview/

Thanks. J

[v-yuezhe-msft · Accepted solution]

@nsy999,

Create new table using DAX below, you can review details in attached PBIX file. If the DAX doesn't return your expected result, please share sample data of your tables and post expected result.

table3 = INTERSECT(Table2,Table1)

Regards,

Lydia

[v-yuezhe-msft · Accepted solution]

@nsy999,

Create new table using DAX below, you can review details in attached PBIX file. If the DAX doesn't return your expected result, please share sample data of your tables and post expected result.

table3 = INTERSECT(Table2,Table1)

Regards,

Lydia

[nsy999 · Accepted solution]

Lydia, thank you. I will give that a go. J

[nsy999]

That worked - thank you. J