DAX for comparing 2 columns (in different tables) to get list of missing values(text)

[Anonymous]

Hi all,

Newbie here needing help with DAX for comparing 2 columns (in different tables) to get list of missing values(text). Can anyone point me in the right direction? Thanks in advance!

For example, 

Table1_CustomerList

CustID

101

102

103

104

105

Table2_OrderList

CustID     OrderID

102          P312

103          P451

105          P231

Output Required:

CustID (with no orders)

101

104

[AlB]

Hi @Anonymous

Try this (assumes no relationships between the 2 tables)

1. Place Table1[CustID] in the rows of a table visual

2. Create this measure:

ShowMeasure =
VAR CurrentID = SELECTEDVALUE ( Table1[CustID] )
RETURN
    IF (
        CALCULATE ( COUNT ( Table2[CustID] ), Table2[CustID] = CurrentID ) + 0 = 0,
        1,
        0
    )

3. Place [ShowMeasure] as a visual filter  and select to show when value is 1

Please mark the question solved when done and consider giving kudos if posts are helpful.

Cheers  

[Anonymous]

@AlB 

Thanks!

Would we be able to get it as a data table instead of a table visual so that we can use the list of CustID without orders for some other DAX measures?

[v-xuding-msft · Accepted solution]

Hi @Anonymous ,

You could try the formula below.

Table 2 = CALCULATETABLE('Table',NOT('Table'[CustID] in VALUES('Table 1'[CustID])))

Best Regards,

Xue Ding

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

[derek0xo]

Hi,

Can you find the missing values based on two critiera? First, it needs to match with the date, then match with a vaue?

Derek