Adding + 0 on Measure Directly Impacts Performance - Memory Issues

[wjkolesar]

Hi - 

I have a measure where I want to display 0 instead of (Blank) in the KPI visual.  I can achieve this by adding "+ 0" onto the end of my measure calculation.  However, whenever I try to place that measure into a table visual, the table visual just spins and never loads and then finally I get the "there's not enough memory to complete this operation."

I have no issues adding the measure to the table viusal when the "+ 0" is not included in the DAX formula.  I've also tried IF(ISBLANK()) as well and I'm getting the same issues.  How could adding this simple criteria in to the formula cause such large impacts to performance?

DAX formula is: 

CALCULATE (
    SUM ( 'Deal'[NPV_of_the_Deal_Full__c] ),
    'Record Type'[Deal Type Selector for Sales Funnel] = "Permanent"
)

I am using the latest version of Power BI desktop - Version: 2.67.5404.581 64-bit (March 2019)

[d_gosbell · Accepted solution]

Adding any constant value to a measure result can cause serious performance and memory issues. The reason for this is that the DAX engine is really good at eliminating blank values, but by adding 0 you've made this measure always return a non blank value.

Suppose you had a customer table with 10,000 customers and a product table with 1,000 products. If your customers only purchase 3-4 times a year and only buy 2-3 products at a time you could create a table of last months sales and expect maybe 3000 customers and 3 products each so 9,000 rows.

BUT if you add a +0 onto the sales amount, now every possible combination of customer and product will return a value so you will get 1,000,000 rows back. And this simple example is just with 2 columns. If you add in the dates (assuming we are filtered to just 1 month) we now have around 30 million rows that the query engine has to process and materialize.

So you either want to be a lot more selective about when you return 0 or maybe create a separate measure for your KPI that is not used elsewhere.