Replace Blanks in Matrix with Zero for huge table

[joshua1990]

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?

[v-yanjiang-msft]

Hi @joshua1990 ,

Has your problem been solved?

Best Regards,
Community Support Team _ kalyj

[tamerj1]

And what is on the rows of the matrix?

[joshua1990]

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

[tamerj1]

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 )

[joshua1990]

@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)

[tamerj1]

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

[joshua1990]

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

[tamerj1]

Can you please share the complete code of your measure?

[joshua1990]

SUM('SalesTrans'[Value])

[tamerj1]

@joshua1990 
Try this might be faster

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

[joshua1990]

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

[tamerj1]

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

[joshua1990]

It's just [Date]. 

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

[tamerj1]

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 )