topic Re: Replace Blanks in Matrix with Zero for huge table in DAX Commands and Tips

Replace Blanks in Matrix with Zero for huge table

joshua1990 — Fri, 01 Apr 2022 07:05:48 GMT

Hi experts!

I have a data model based on 2 dimensional tables (Calendar and Article Table) and 1 transactional table (Sales).

The transactional table contains several million rows.

I have a matrix visual that shows me per article (rows) for the past 15 weeks (columns) the sales.

Since not every article shows sales for each week I have a lot of blank spaces in the matrix.

I would like to replace this blank spaces with a '0'.

I tried it using Measure + 0, but this is not working. The dax measure is running, and running without any result when I add +0 to the measure. Is this is related to the number of rows?

Any other sugestions here?

Re: Replace Blanks in Matrix with Zero for huge table

tamerj1 — Fri, 01 Apr 2022 09:00:35 GMT

Hi joshua1990
That is because the engine is trying to optimize the caculations. Once it scans the sales table it will decide in which cells it shall perform calculation and which ones to skip. When you add +0 you are actually forcing the engine to perform the caclulation for all cells and hence more CPU time will be consumed.
You may try

Sales Amount = VAR SaleAmount = SUMX ( Sales, Sales[Amount] * Sales[Qty] ) RETURN IF ( ISBLANK ( SaleAmount ), 0, SaleAmount )

Re: Replace Blanks in Matrix with Zero for huge table

joshua1990 — Fri, 01 Apr 2022 12:21:44 GMT

tamerj1 Thanks a lot for this explanation! Do I need SUMX? Or this just an example measure? My original measure is a simple CALCULATE(SUM(Sales[Qty]), Filter X)

Re: Replace Blanks in Matrix with Zero for huge table

tamerj1 — Fri, 01 Apr 2022 12:24:00 GMT

joshua1990
No you don't need. This is just an example as I did not what your measure is.

Re: Replace Blanks in Matrix with Zero for huge table

joshua1990 — Fri, 01 Apr 2022 12:37:25 GMT

tamerj1 : Thanks, but the DAX Measure is still running. Nothing happens. In general the report is fast using the simple sum measure. Using IF and ISBLANK ist not working here as well.

Re: Replace Blanks in Matrix with Zero for huge table

tamerj1 — Fri, 01 Apr 2022 12:43:01 GMT

Can you please share the complete code of your measure?

Re: Replace Blanks in Matrix with Zero for huge table

joshua1990 — Fri, 01 Apr 2022 12:50:40 GMT

SUM('SalesTrans'[Value])

Re: Replace Blanks in Matrix with Zero for huge table

tamerj1 — Fri, 01 Apr 2022 13:01:25 GMT

joshua1990
Try this might be faster

Sales Amount = VAR SaleAmount = SUM ( 'SalesTrans'[Value] ) RETURN SaleAmount * DIVIDE ( SaleAmount, SaleAmount, 0 )

Re: Replace Blanks in Matrix with Zero for huge table

joshua1990 — Fri, 01 Apr 2022 13:06:55 GMT

tamerj1 Thats weird. I still have blank values when I use you new measure

Re: Replace Blanks in Matrix with Zero for huge table

tamerj1 — Fri, 01 Apr 2022 13:26:14 GMT

What is the nam of the date column in your sales table?

Re: Replace Blanks in Matrix with Zero for huge table

joshua1990 — Fri, 01 Apr 2022 13:28:42 GMT

It's just [Date].

The calendar table contains the week information that I display on columns in the matrix

Re: Replace Blanks in Matrix with Zero for huge table

tamerj1 — Fri, 01 Apr 2022 13:34:00 GMT

And what is on the rows of the matrix?

Re: Replace Blanks in Matrix with Zero for huge table

joshua1990 — Fri, 01 Apr 2022 13:46:52 GMT

tamerj1 : Article on row, week on column and sales as value

Re: Replace Blanks in Matrix with Zero for huge table

tamerj1 — Fri, 01 Apr 2022 14:01:23 GMT

Tey this

Sales Amount = VAR FirstSalesDatea = CALCULATE ( MIN ( 'SalesTrans'[Date] ), REMOVEFILTERS () ) VAR LastSalesDate = CALCULATE ( MAX ( 'SalesTrans'[Date] ), REMOVEFILTERS () ) VAR CurrentDate = MAX ( 'Date'[Date] ) VAR ForceZero = FirstSalesDate >= CurrentDate && CurrentDate <= LastSalesDate VAR SaleAmount = SUM ( 'SalesTrans'[Value] ) RETURN SaleAmount + IF ( ForceZero, 0 )

Re: Replace Blanks in Matrix with Zero for huge table

v-yanjiang-msft — Wed, 13 Apr 2022 03:16:08 GMT

Hi joshua1990 ,

Has your problem been solved?

Best Regards,
Community Support Team _ kalyj