Forum Discussion

nanat's avatar
nanat
Frequent Visitor
8 years ago
Solved

Adding Filter on Countrows and Relatedtable

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.

 

Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

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

2 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    8 years ago

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