Replacing blanks with 0 in matrix visual

[Anonymous]

Hi, 

I currently have this matrix with many blanks in dates where there are no values. The original data does not display zero, so I am trying to find a way to change the blanks in this visual to display zeroes instead. Would anyone know how to go about this? Thank you, any help is appreciated!

[Samarth_18]

Hi @Anonymous ,

If its values directly coming from the column then you can try any of the below code by creating a measure.

Measure = max('table'[column])+0

Measure = coalesce(max('table'[column]),0)

Thanks,

Samarth

[Anonymous]

Hi, 

The issue is that my original data does not contain the missing values. It skips over those. I tried the above measures but it displays all the names instead of counting them. This is how it is currently organized: 

I would go to the original data and trasnform the values to zero, but not all days have values for each location in the source data. 

Thank you for any help!  

[Thejeswar]

Hi @Anonymous ,

You should first create a table with two columns which will look more like a cross join between Dates and Location. i.e. It should have all locations for all dates.

This you should left join with your dataset, thereby introducting NULLs into your dataset.

Then you can try using the DAX that @Samarth_18  gave you

Regards,

[Anonymous]

Hi @Thejeswar, 

That is what I ideally wanted, to transform the source data so that it would include null values where there were no values recorded. I am just starting to get experience with DAX and Power Query, so I am not sure how I would go about doing that. Would I create this new table under Modeling? I've been manually extracting tables from the visuals to use as source data that helps me manipulate the output but it's very a inefficient method. 

[Thejeswar]

Hi @Anonymous ,

You can use a CROSSJOIN() DAX to get this

new_table = CROSSJOIN(VALUES(Table1[Column1]),VALUES(Table1[Column2]))

The Above DAX function can be used in the Data section of the Power BI Desktop.

The Above DAX will crossjoin Column1 with Column2.

For example, if Column1 has 3 records and Column2 has 2 records, then your new_table will have totally 6 records (3  * 2)

Try it out in your case 

new_table = CROSSJOIN(VALUES(Table1[Dates]),VALUES(Table1[Location]))

Regards,

[Samarth_18 · Accepted solution]

@Anonymous , In this case you could use below formula. 

Measure = calculate(count(table[name]))+0

[Anonymous]

Hi @Samarth_18 

I tried that measure but this was my output: 

Edit: This works! I will just change the name of the Measure Column, thank you!