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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Raj910
Microsoft Employee
Microsoft Employee

Display 0 instead of Blank values in Matrix(for underlying missing data)

Hi,

I basically want to show 0 instead of blanks in the Matrix visual which is getting data from a single table(both facts and attributes in same table unfortunately and not allowed to alter the table structure)

While doing so the data for few products are missing in the database for few months which are displaying as blanks.

In the below Matrix, few productNames dont have Users and Rate for Jan/Feb/Mar/Apr etc and hence showing as blank.

I am not allowed to add blank or null rows for the missing data in the database.

ISBLANK,COALESCE, IF logic nothing seem to work as there is no data in the db.

I tried adding seperate CalendarDate/ProductDims and create one to many relationship and include it the matix rows/columns but it dint work out.

Can anyone suggest any method to show 0 instead of blanks?

retentionpost.png

 

Thanks.

8 REPLIES 8
amitchandak
Super User
Super User

@Raj910 

+0 in the formula should work

Measure = calculation(...) +0

Also check do you need to do this setting

ShowItemwithoutdata.JPG

Hi, I have tried both the options but it doesn't work as there is no underlying data. I even tried isblank()+0 but no luck.

@Raj910 ,Can you share sample data and sample output.

Here is the sample data:

FactProduct

ProductNameDateCount
P1Feb20191000
P1Mar20192000
P1Apr20193000
P2Apr2019

3000

P3Apr20194000
P4Mar20194000
P4Apr20195000

 

CurrentOutput: Matrix Visual

ProuductNameFeb2019Mar2019Apr2019
P11000      20003000
P2  3000
P3  4000
P4 40005000

 

 

ExpectedOutput

ProuductNameFeb2019Mar2019Apr2019
P11000      20003000
P203000
P3004000
P4040005000

 

This sample is a simplfied matrix where as in the actual there are two measures involved for each product/month:

UserCountMeasure = COALESCE((CALCULATE(SUM(UserCount]), [Filter] = 1))+0,0)
UserCountPercent = IFERROR((CALCULATE(SUM([UserCount]), [Filter] = 1)
/CALCULATE(SUM([UserCount]), [Filter] = 0, ALL(ProductName), ProductName = "All")),0)+0

 

Any kind of help/approach/suggestion to have 0 displayed is highly appreciated.

 

Thanks.

dax
Community Support
Community Support

Hi @Raj910 , 

If you want to show 0 in matrix, you need to create table and create relationship between tables. You could refer to my sample for details.

Best Regards,
Zoe Zhi

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

Raj910
Microsoft Employee
Microsoft Employee

@dax Sorry for the delay, creating a seperate table and linking both worked. Thanks.

Raj910
Microsoft Employee
Microsoft Employee

Hi @dax ,

 

Thanks for the pbix file. As I already mentiioned in my original post

"I tried adding seperate CalendarDate/ProductDims and create one to many relationship and include it the matix rows/columns but it dint work out" and I am not getting the expected results for the measures I use.

 

Thanks.

dax
Community Support
Community Support

Hi @Raj910 , 

 I didn't find the [filter] and "All" in your sample, so could you please show more samples to me?

Please do mask sensitive data before uploading.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors