Displaying 0s in Summary Matrix Not Working Correctly

[gmat]

I'm currently working on a summary dashboard in PowerBI that displays data from two business units over the last 12 months. I've divided the dashboard into sections, with the first section focusing on one business unit and only showing data for March 2023 and October 2023.

The second section is dedicated to the other business unit, displaying data for each month (because it is available).

My challenge is ensuring uniformity in the display, even when there is no data in the top BU . I attempted to insert a new measure to count certain data points and included 0s for cases with no data:

 Count + 0 = calculate(count(Query1[COUNT]))+0

(top BU)

However, this approach didn't work as expected. It did pull in display 0s but it interfered with my date filters, pulling in data for the last two years, including January 2023, despite having a relative date filter (in the last 1 year). It is also pulling in all of 2024 months (Jan 24 - Dec 24) with 0s. 

I'm puzzled about why it's pulling in all of these other months and would appreciate any help or insights you can provide to resolve this issue. If you need additional details about my setup, feel free to ask.

[v-zhengdxu-msft]

Hi @gmat 

Maybe you can try the COALESCE() function which returns the first expression that does not evaluate to BLANK. If all expressions evaluate to BLANK, BLANK is returned.

Here is the sample:

Then add a measure:

Measure = CALCULATE(COUNTROWS('Table'),'Table'[Name]="DD")

The result:

Then change the measure:

Measure = COALESCE(CALCULATE(COUNTROWS('Table'),'Table'[Name]="DD"),0)

The result is as follow:

If the above one can't help you, could you please provide more raw data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples? It would be helpful to find out the solution. You can refer the following links to share the required info:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

To report on things that are not there you need to use disconnected tables and crossjoins.  In your scenario you need a disconnected calendar table for your X axis.

[gmat]

I get the idea here but I am unable to use a disconnected calendar table for my x axis. PowerBI canot use the calendar table without a relationship.

[lbendlin]

you can use two calendar tables. One for the data model and one for the X axis.

[gmat]

Can you walk me through how to do this? Not sure I know how to.

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data 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.

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

[gmat]

Below is a example data set that is needed to put this view together. When I try to create a matrix on this data set. This is what I am able to build in my matrix. I want to be able to show the months that are missing data Feb - Sep 2023. 

Category	Date	Count
New	1/31/2023	7
New	2/28/2023	8
New	9/30/2023	9
New	10/31/2023	10
New	11/30/2023	11
New	12/31/2023	12
Used	1/31/2023	13
Used	2/28/2023	10
Used	9/30/2023	8
Used	10/31/2023	9
Used	11/30/2023	51
Used	12/31/2023	3
New	1/31/2024	22
Used	1/31/2024	20

[lbendlin · Accepted solution]

[gmat]

This worked, thanks so much.