Showing records without a match in 1 to many relationship

[hooflak]

Hi all,

Appreciate any help on this.

I have two tables 

Customer and CustomerNote

One customer can have multiple notes and the tables are joined on customerid in a 1 to many.

I want to display customers that have no notes.

If I don't select a field from CustomerNote table I can see customers in Customer table that don't have a note but as soon as I select a field from CustomerNote table also only records that match a note are shown.

How can I set up a filter so I can get a list of Customers that do not have any note records in CustomerNote table?

Thanks in advance

[danextian · Accepted solution]

Hi @hooflak ,

In the CustomerNote table, you can create a calculated column that returns the number of note per customer for a particular customer/customer id. So if you have 10 rows for that customer, each row will have the same count. You can use then this column/formula to identify those that do no have a note.

Note Count Per Customer =
CALCULATE (
    COUNTROWS ( CustomerNote ),
    ALLEXCEPT ( CustomerNote, CustomerNote[CustomerID] )
)

 or

Customer without a note =
CALCULATE (
    COUNTROWS ( CustomerNote ),
    ALLEXCEPT ( CustomerNote, CustomerNote[CustomerID] )
) < 1