Count total distincts from two tables

[wanyunyang]

I have one order table with OrderNo, one invoice table with InvoiceNo and OriginalOrderNo.

I would like to count the total distinct order numbers from both tables. I'm using:

Count(distinct OrderNo) + Count(distinct OriginalOrderNo)

But the problem is, there would be partial invoices. For example, OrderNo1 has been partially invoiced, it will still be in order table with OrderNo1, as will as be in invoice table as InvoiceNo1 and OriginalOrderNo1. So it should be counted once. However, in my function the result is 2. 

How should I update my function? Any advice helps! Thanks in advance!

[jwjackso]

Try the P Function 

Count(distinct OrderNo) + Count({<OriginalOrderNo-=P(OrderNo)>}distinct OriginalOrderNo)

[wanyunyang]

Hi,

Thank you for your reply! I tried this function but didn't work. I think it's because I have set analysis like:

Count({<OrderDate={'01/01/2020'}>}distinct OrderNo) +

Count({<OriginalOrderNo-=P(OrderNo),OrderDate={'01/01/2020'}>}distinct OriginalOrderNo)