Forum Discussion
how to replace blank fields to zero in matrix visual
Hi I have a table
| Itemnumber | Date | Value |
| A | 12/09/2022 | 20 |
| A | 13/09/2022 | 50 |
| A | 14/09/2022 | 60 |
| B | 13/09/2022 | 40 |
| B | 14/09/2022 | 10 |
| C | 12/09/2022 | 70 |
| C | 14/09/2022 | 40 |
| D | 13/09/2022 | 50 |
and I want the blank fields to be filled up with zero, so I can perform calculations, but I only get blanks instead of zero
| Itemnumber | 13/09/2022 | 14/09/2022 |
| A | 50 | 60 |
| B | 40 | 10 |
| C | 0 | 40 |
| D | 50 | 0 |
Hi,
thank you for sharing.
In my opinion, data model has to be created by using item dimension table and date dimension table.
Please check the attached pbix file and the below picture.
9 Replies
- Jihwan_Kim
Super User
Hi,
One of ways to achieve this is to create a measure something like below, and put it into the matrix visualization.
New measure: = SUM(Table[Value]) + 0
Hi, I already tried this, but it doesn't work. The blank fields still remain blank.
- Jihwan_Kim
Hi,
Thank you for your feedback, and please share your sample pbix file's link here, and then I can try to look into it to come up with a more accurate solution.
Thanks.
- Jihwan_Kim
Hi,
thank you for sharing.
In my opinion, data model has to be created by using item dimension table and date dimension table.
Please check the attached pbix file and the below picture.
Wow, this works. Many thanks!
I now added a Difference - measure to get a view on the difference in numbers between 2 days.
I got the tip from another post:
Difference = Var vPreviousDay = CALCULATE(Sheet1[expected measure:], FILTER(Sheet1,Sheet1[Days since]=MIN(Sheet1[Days since])))Var vPreviousDay1Back = CALCULATE(Sheet1[expected measure:], FILTER(Sheet1,Sheet1[Days since]=MIN(Sheet1[Days since])+1))RETURN vPreviousDay-vPreviousDay1BackBut in the final matrix, I would in the lowest row expect -50 instead of +50, because I substract 0 minus 50.Do you have any advice on this matter? I add again the link to the pbix.Many thanks.
