topic Re: how to replace blank fields to zero in matrix visual in DAX Commands and Tips

how to replace blank fields to zero in matrix visual

IlseV — Fri, 16 Sep 2022 08:26:28 GMT

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

Re: how to replace blank fields to zero in matrix visual

Jihwan_Kim — Fri, 16 Sep 2022 08:34:54 GMT

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

Re: how to replace blank fields to zero in matrix visual

IlseV — Fri, 16 Sep 2022 08:42:51 GMT

Hi, I already tried this, but it doesn't work. The blank fields still remain blank.

Re: how to replace blank fields to zero in matrix visual

Jihwan_Kim — Fri, 16 Sep 2022 08:45:20 GMT

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.

Re: how to replace blank fields to zero in matrix visual

IlseV — Fri, 16 Sep 2022 08:56:17 GMT

Hi, I can't add pbix files here

Re: how to replace blank fields to zero in matrix visual

Jihwan_Kim — Fri, 16 Sep 2022 09:44:42 GMT

Hi,

please try to share Onedrive link, Googledrive link, Dropbox link, or any other link that allows to share the file, instead of attaching the file to here.

Thanks.

Re: how to replace blank fields to zero in matrix visual

IlseV — Fri, 16 Sep 2022 09:52:36 GMT

https://drive.google.com/file/d/12pYfxuAAOB_bL65QkX5RhVZSkEaA4PYU/view?usp=sharing

Re: how to replace blank fields to zero in matrix visual

tamerj1 — Fri, 16 Sep 2022 10:35:06 GMT

Hi IlseV
You need to have independent dimention tables as follows. Please refr to atached sample file

Measure = SUM ( FactTable[Value] ) + 0

Re: how to replace blank fields to zero in matrix visual

Jihwan_Kim — Fri, 16 Sep 2022 10:38:16 GMT

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.

Re: how to replace blank fields to zero in matrix visual

IlseV — Fri, 16 Sep 2022 11:55:57 GMT

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-vPreviousDay1Back

But 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.

https://drive.google.com/file/d/1o7RTfcFyD0DiYLZkZAa4gN17T-i0Equc/view?usp=sharing

Many thanks.