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

[Raj910]

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?

Thanks.

[amitchandak]

@Raj910 

+0 in the formula should work

Measure = calculation(...) +0

Also check do you need to do this setting

[Raj910]

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.

[amitchandak]

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

[Raj910]

Here is the sample data:

FactProduct

ProductName	Date	Count
P1	Feb2019	1000
P1	Mar2019	2000
P1	Apr2019	3000
P2	Apr2019	3000
P3	Apr2019	4000
P4	Mar2019	4000
P4	Apr2019	5000

CurrentOutput: Matrix Visual

ProuductName	Feb2019	Mar2019	Apr2019
P1	1000	2000	3000
P2			3000
P3			4000
P4		4000	5000

ExpectedOutput

ProuductName	Feb2019	Mar2019	Apr2019
P1	1000	2000	3000
P2	0	0	3000
P3	0	0	4000
P4	0	4000	5000

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]

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]

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

[Raj910]

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]

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.