topic Re: How to find distinct count of an id in first table which is not present in the second table in DAX Commands and Tips

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

Akshaymanjunath — Mon, 17 Jun 2024 05:29:14 GMT

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

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

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

vicky_ — Mon, 17 Jun 2024 06:39:32 GMT

I would try something like the below:

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

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

MattAllington — Mon, 17 Jun 2024 21:18:33 GMT

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

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

xifeng_L — Tue, 18 Jun 2024 14:37:53 GMT

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])))