Matrix Visual - Not Displaying Zeros

[L4TET]

Hello All! 

First and foremost I apologize as it seems like this question has been asked multiple times with working answers...for everyone else but me. I have spent the better part of the week crawling the forms, using co-pilot, chatgpt, google flash, you name it to try and get an answer however they end up becoming a circular conversation to no avail. 

Background:

I am attempting to visual specific critrea (in this case it is a type of offense) which has two parts. Main Offense (overarching) and Sub Offense (more specific). They are all tracked by specific occurrence (offense number) which is unique to each one. Now to build on that, I have built a matrix to show the Fiscal YY QTR in the columns portion which DOES successfully show each quarter. For the rows I have offense and sub offense located in the rows, to which you can drill down successfully. Now the real issue, there are blanks for the quarters that a specific offense didn't happen. What I am trying to reflect that it shows zeros instead of blanks. All my original data is in the same table. 

I have attempted (with the assistance of AI of course) functions such as cross join, COALESCE, IF),0 and everything in between. I can accomplish the same task in excel with no problem using conditional formatting, but I cannot seem to get the same type of effect in the MATRIX visual in PowerBI. 

How do I accomplish this? Please make my mind stop reeling! I have attached a photo as reference. 

[L4TET]

@ExcelMonke 

I apolgize for the delayed response, as I am a new user on the forums, there is a delay in my ability to post. I still am unable to resolve this issue. Based on your response, I feel as if I have contributed/caused this issue inadventerly. So my Fiscal Year & Quarter are stored as text from the original excel document (its how the system exports it) so they are in formats as 22 Q1, 22 Q2, etc etc. However I do have specific dates. After using some AI, I have toyed with creating a date table with custom quarters, and even with the custom quarters, I still have blanks instead of zeros. I feel like this is something really simple that I am completely overlooking. Thank you for everything so far and I look forward for your response!

[Anonymous]

Hi @L4TET,

In Power BI, Matrix visuals only show combinations that exist in the data. So if an Offense/SubOffense didn’t occur in a specific Fiscal Quarter, that combination is missing — resulting in a blank, not a zero. A zero means the data exists but the value is 0, while a blank means the data doesn't exist at all.

Regards,

Vinay Pabbu

[ExcelMonke]

@L4TET 
Hmmm that is really strange - I just pulled up the sample data set again and recreated it, but I am getting the intended result (showing zeroes) again.

Can you share your measure to better see what is going on? Also, is your data format in number or text? If it is text (and for whatever reason it has to be a text format), you can try 

Measure = 
IF( ISBLANK ( [Measure] ), "0", [Measure])

[L4TET]

@ExcelMonke, 

Thank you for being the first to take a shot at my plight. I have implemented your measure, with no success. I am still facing the same blank spaces as is attached in my photo. I used this measure in the same table where all of that data is stored. I have also tried to implement different variations with a separate table, with relationships via cross join. Any other ideas you may have would be greatly appreciated! 

[ExcelMonke · Accepted solution]

Hello @L4TET ,

I pulled together a rough dataset to hopefully try and recreate your data and the problem. You can consider the following DAX for your Offense count per quarter:

Offense Count = 
COALESCE(COUNT('Table (2)'[SubOffense]),0)

This is how my result Matrix looks like:

My initial data looks something like this:

Hopefully this helps!