Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
PBI_alma
Regular Visitor

Filter is not working with display of 0-values

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:

 

 

Tab1Tab2Tab3Tab4
NameCity_ID
ProductUser_ID
Department User_IDSales_Amount
City_ID
 City_IDSales_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

3 REPLIES 3
PBI_alma
Regular Visitor

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

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

 

lbendlin_0-1700514703128.png

 

lbendlin
Super User
Super User

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...

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.