Problem displaying records with no data (as 0) in Power BI visual

[andrew_k]

Hi Community

I am struggling to get my Power BI visual to display items (which have no data) as '0'.

• My data model contains a hierachy (1. Business Area, 2. Department)
• I have a total of 36 Departments.
• These department fall into a Business Area of which there are 17.
• Each Department has a certain number of software 'Applications' and I want to count them and roll it up to the Business Area.
• But some Departments have no Applications, but I want this shown in my report as a '0'.

The problem i'm having is this:

In my table (or matrix), I add the Business Area and Department. Then I add my measure (Total Applications) which is just a count of all applications.

Total Applications = COUNT(Applications[Application ID])

When I add 'Total Applications', the table gets filtered to exclude items with no data.
If I right click on 'Department' in the field well and select 'Show items with no data', the items WILL be displayed, but as blank.
I have also tried writing a different DAX expression:

Total Applications =
VAR __Calc = COUNT(Applications[Application ID])
RETURN
IF(ISBLANK(__Calc),0,__Calc)

But this creates an even worse problem. For some reason, every department is displayed under every business area (like a cartesian join in SQL) so 36*17.

All I want to do is display a '0' instead of a blank if that's possible.

Any help would be greatly appreciated.

This is a table with my full hierarchy without any value:

And this is what happens when I try to display my data with a zero using the DAX with a 0 if blank. You can see that the Department is duplication for each business area.

Update:

Here is my data model. I have broken down my Hierarchy: Employees >> Departments >> Business Areas.

If I de-normalise it and remove the Business Area and Department tables and roll it up to the Employees table, then the problem dissapears and the data doesn't duplicate.

Example File below. This is a test file with 100% dummy data and random names which replicates my real world problem.
I tried to upload it but am not a super user so don't have permisssion so I am attempting to share it via my proton drive.
https://drive.proton.me/urls/AYPD577ZV0#GQhdFhUgNiRK 

[foodd]

Hello @andrew_k ,  a number of community members have offered advice and a solution to you. Until now, they have not asked for your work-in-progress Power BI Desktop file and the source in Excel format to aid in transparency.  Remember to adhere to the decorum of the Community Forum when asking a question.

Please provide your work-in-progress Power BI Desktop file (with sensitive information removed) that covers your issue or question completely in a usable format (not as a screenshot).

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.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

This allows members of the Forum to assess the state of the model, report layer, relationships, and any DAX applied.

[elitesmitpatel]

Please share the file as 2- 3 solution did not worked , ofcourse share a dummy data set with no private and important information 

[andrew_k]

Now shared via my proton drive as I was unable to upload it.

[elitesmitpatel]

just upload on google drive or one drive and share the link in the comment by attaching the link in the below mention image

[vigneshba]

Hi @andrew_k  - 

Please try below approach using DAX. Let me know if it works, refer image 1. Before that ake Sure you enabled the option in the Dimension column to "Show items with no data", refer image 2

Value =
IF (
    CALCULATE ( SUM/COUNT ( your_actual_Value ) ) = BLANK (),
    0,
    CALCULATE ( SUM/COUNT ( your_actual_Value ) )
)

  

[andrew_k]

Unfortunatly that didn't work. Same problem so I have shared my file.

[Ashish_Mathur]

Hi,

Does this work?

Measure = coalesce([Total applications],0)

[andrew_k]

Thanks for your response. I have updated my post with my data model.
Unfortunatley it doesn't work with my current model and the department still duplicates.
However, if I denormalise my model then it works. But I would still like to find out why i'm having this problem. I'm sure Power BI supports this.

[Ashish_Mathur]

Hi,

I will have to see your file.

[andrew_k]

Now shared via my proton drive as I was unable to upload it.

[Ashish_Mathur]

Hi,

The reason you are facing this problem is that there are different Dim Table of Employees, Department and Business Areas. I think the solution would be to set the cross filter direction to Both but i am not sure.  Hope someone can help here.

[andrew_k]

The Employees table did previously contain the department and business areas but I split it out to further normalise my model. I did try to experiment with the cross filter direction but this didn't work.

[aduguid]

Total Applications =
VAR __Calc = COUNT(Applications[Application ID])
RETURN
__Calc + 0

[andrew_k]

Thanks for your response. I have updated my post with my data model.
Unfortunatley it doesn't work with my current model and the department still duplicates.
However, if I denormalise my model then it works. But I would still like to find out why i'm having this problem. I'm sure Power BI supports this.