Counting blanks in a Company > Department > Item relationship

[Vlad_M_]

Hi

I've ran into the following issue. I have a table for Companies, Departments and Items.

Items both have a Company and Department column. All items has a company, but they don't necessarily have a department.

Companies might have items with departments and without departments attached.

Like so:

Companies

ID

Departments

ID

CompanyID

Items

ID

CompanyID

DepartmentID

I want to showcase a donut chart, where a count of all items for each department are displayed for a company. When showing a summed chart of all companies, it counts the items with "blank" in department and shows them as "(Blank)"-department, I guess since there still is a relationship to the company - BUT - when I try to show the same chart for a single company, I don't get any results when a company doesn't have any departments, or if the items are "blank" in their "DepartmentID".

How could I make a statement something like:

"Get all items in a company for each department, if DepartmentID is empty count the item as a "(Blank)" department"

I hope my issue makes sense, any help would be greatly appreciated - thanks!

[v-cgao-msft · Accepted solution]

Hi @Vlad_M_ ,

Please check if this file helps:

relationships:

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

[Vlad_M_]

Hey Gao

It was exactly what I meant! Thanks!