Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
Proud to be a Super User!
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
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |