How to find distinct count of an id in first table which is not present in the second table

[Akshaymanjunath]

consider 2 tables orders and sales having conection with each other and below are the table details

consider table 1 : orders

below are fields of orders table

which contains

id

name

order date

consider table 2 : sales

below are fields of sales table:

idsales

quantity

ship date

now find the distinct count of "id" in the orders table that is not present in the sales table using dax

note:

is the below formula correct

distinct count not in table = CALCULATE(DISTINCTCOUNT(orders[id]), NOT(orders[id] IN VALUES(sales[id])))

@MattAllington @xifeng_L @lbendlin 

[vicky_ · Accepted solution]

I would try something like the below:

distinct count not in table = COUNTROWS(EXCEPT(VALUES(orders[id]), VALUES(sales[id])))

[MattAllington]

The type of relationship between the table matters. What is the cardinality and direction of filter propagation?  A screen should of the model view is normally easiest 

[vicky_ · Accepted solution]

I would try something like the below:

distinct count not in table = COUNTROWS(EXCEPT(VALUES(orders[id]), VALUES(sales[id])))

[xifeng_L · Accepted solution]

That's fine, but I think it might be better to change VALUES to ALL, so as to avoid the effects of some extraneous filtering.

distinct count not in table = COUNTROWS(EXCEPT(ALL(orders[id]), ALL(sales[id])))