Adding Filter on Countrows and Relatedtable

[nanat]

Hi,

I need some help please. I have 2 tables (All Customers, Product Items). These 2 tables are linked by the unique Customer ID with All Customers as the 1 side and Product Items as the many sides.

I am trying to get a calculated column in All Customers to achieve the unique number of invoiced product orders each customer has placed. Please note that Product Items list the items customers have ordered but do not relate to orders - meaning 3 product items can equal to 1 order.

What I have so far is:

Product Order per Customer = 
COUNTROWS(
    RELATEDTABLE('Product Items Internal ID')
)

This gives me the number of product items each customer has placed but I need the number to be the unique number of product orders and also a filter which it will filter the Status of Product Items to be "Invoiced". 

I have tried a few times but the filter would not work. How can I add in a filter that will work for what I am after.

Thank you.

[Zubair_Muhammad · Accepted solution]

@nanat

I think something along these lines should work

Product Order per Customer =
CALCULATE (
    DISTINCTCOUNT ( 'Product Items Internal ID'[Products] ),
    'Product Items Internal ID'[status] = "invoiced"
)

[nanat]

Hi @Zubair_Muhammad,

Thank you!