Filter is not working with display of 0-values

[PBI_alma]

Hello everyone,

I have a question that I'm stuck on and I hope you can help me.

I have a data model with the following 4 tables:

Tab1	Tab2	Tab3	Tab4
Name	City_ID	Product	User_ID
Department		User_ID	Sales_Amount
City_ID		City_ID	Sales_ID

These tables are each linked with the bold columns Tab1 to Tab2 (n:1), Tab2 to Tab3 (1:n) and Tab3 to Tab4 (1:n), filter direction must be on both sides in each case.

Now I have a table in the report that shows me the information from Tab3. If I filter according to the department from Tab1 in the slicer, the values from Tab3 are displayed correctly. Now I would like to add a measure to the table that counts the Sales_IDs and also displays the zeros. By displaying the zeros, however, products are displayed in the table from tab 3 that have no connection to the department and are (correctly) counted with zero. But I do not want to see these products without the connection to the department filter.
The question is, which departments have not made any sales - Number of Sales_IDs = 0

The measure used so far:

IF(ISBLANK(

CALCULATE(DISTINCTCOUNT('Tab4'[Sales_ID])

)), "0", CALCULATE(DISTINCTCOUNT('Tab4'[Sales_ID])

))

Do you have a suggested solution here? If I put everything in one table in the data model, it works of course, but I would like to avoid the data redundancies.

Thank you for your efforts,
best regards

[PBI_alma]

@lbendlin Thank you for your answer, you can find the datamodel and more details  here.

[lbendlin]

your data model does not support your question. Departments have no relationship to Sales.

[lbendlin]

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
If you are unsure how to do that please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

If you want to get answers faster please refer to https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...