Need to Show Zero values in the blank Matrix

[Surya31223122]

Hi Team,

I have attched is my data model and matrix visulization in which i want replace all blank values with Zero . Below are the my dimensions  and measures considers .

Dimensions :
1) Facility Parameter[Facility Parameter]
2) AB00170_FACT_PBI[TG_DIV]
3) AB00170_FACT_PBI[TIER_DIV]
4) Agg Parameter [Agg Parameter ]
5)AB00170_FACT_PBI[CLS_DIV]

6)AB00170_FACT_PBI[ITM_CD]

columns

 Day Table[Date_Display] 

Measure :

CALCULATE(round(SUM(AB00170_FACT_PBI[AMT])/1000,0),AB00170_FACT_PBI[Order day]<>"00")

[MarkLaf · Accepted solution]

Take a look here: https://www.sqlbi.com/articles/how-to-return-0-instead-of-blank-in-dax/

Basically, you want to define the criteria for when 0 appears instead of blank. Just adding 0 will cause the engine to evaluate 0 on the crossjoin of your dimensions, which is very expensive and can hit your memory limits on larger models, as you saw.

Usually, for simple cases, you can just test if a dimension exists in the filter context or not. Try something like:

Measure=
< DAX returning blanks in some cases >
+ IF( NOT ISEMPTY( 'Facility Parameter' ), 0 )

[v-hashadapu]

Hi @Surya31223122 , Hope you're doing fine. Can you confirm if the problem is solved or still persists? Sharing your details will help others in the community.

[v-hashadapu]

Hi @Surya31223122, Hope you're doing okay! May we know if it worked for you, or are you still experiencing difficulties? Let us know — your feedback can really help others in the same situation.

[v-hashadapu]

Hi @Surya31223122 , Thank you for reaching out to the Microsoft Community Forum.

As suggested by @Shahid12523 and others, using +0 or COALESCE will technically replace blanks with zero, but in a large model like yours this forces Power BI to evaluate the entire crossjoin of all dimensions, which is why you’re hitting the Resources Exceeded error.

As suggested by @MasonMA , the better approach is to address this upstream by pre-aggregating your fact table or removing irrelevant rows like Order day = "00" in Power Query so the engine has less to process. As suggested by @MarkLaf , you should also avoid blindly adding zeros everywhere and instead use conditional logic, so zeros only appear in valid contexts. And as suggested by @danextian , it’s important to understand how VertiPaq optimizes blanks, forcing zeros eliminates that optimization, so adding conditions such as limiting to the selected date range or active entities helps keep performance stable. Taken together, this approach ensures you get the zeros you want without overloading the model.

Thank you @danextian , @MarkLaf & @MasonMA for your valuable responses and continued contribution in the community.

[v-hashadapu]

Hi @Surya31223122 , hope you are doing great. May we know if your issue is solved or if you are still experiencing difficulties. Please share the details as it will help the community, especially others with similar issues.

[danextian · Accepted solution]

Hi @Surya31223122 

You can simply use [your measure] + 0 but there is a caveat - this can be very expensive on large datasets. The VertiPaq engine in Power BI improves performance by skipping unnecessary calculations when working with blank (null) values. However, if a measure forces zeros, the formula engine is required to perform a full cross-join of the dimension tables in the visual, evaluating each row explicitly. This increases query execution time instead of scanning only the rows where records for the combination of dimension attributes actually exist. It can also cause unexpected data points to appear. For example, an entity that was deactivated years ago may still show up for a selected period, but with zero values. A common remedy is to add a condition when to make zero appear - of course, the more dimension tables involved, the more conditions might be needed. The measure below is a simple example:

VAR _minDate =
    MINX ( ALLSELECTED ( datesTable ), DatesTable[Date] )
VAR _maxDate =
    MAXX ( ALLSELECTED ( datesTable ), DatesTable[Date] )
RETURN
    IF (
        DatesTable[Date] >= _minDate
            && DatesTable[Date] <= _maxDate,
        [your measure] + 0
    )

[MarkLaf · Accepted solution]

Take a look here: https://www.sqlbi.com/articles/how-to-return-0-instead-of-blank-in-dax/

Basically, you want to define the criteria for when 0 appears instead of blank. Just adding 0 will cause the engine to evaluate 0 on the crossjoin of your dimensions, which is very expensive and can hit your memory limits on larger models, as you saw.

Usually, for simple cases, you can just test if a dimension exists in the filter context or not. Try something like:

Measure=
< DAX returning blanks in some cases >
+ IF( NOT ISEMPTY( 'Facility Parameter' ), 0 )

[MasonMA]

@Surya31223122 

If they all return 'Resource Exceeded' that indicates the fact table might be huge and there's heavy calculation context.

In this case i'd suggest pre-aggregating and removing 'Order day = "00"' rows in Power Query if they are irrelevant so that your Measure becomes something like 'DIVIDE( SUM ( AB00170_FACT_PBI[AMT] ), 1000, 0 )' and blanks will naturally be zeroed with +0 or COALESCE.

[Shahid12523]

Measure_Display =
COALESCE(
CALCULATE(
ROUND(SUM(AB00170_FACT_PBI[AMT]) / 1000, 0),
AB00170_FACT_PBI[Order day] <> "00"
),
0
)

[Surya31223122]

even we give some filters, we are getting below error

[Royel]

Hi @Surya31223122 just add + 0 at the end of your measure 

_Measure = 
CALCULATE (
    ROUND ( SUM ( AB00170_FACT_PBI[AMT] ) / 1000, 0 ),
    AB00170_FACT_PBI[Order day] <> "00"
) + 0

Or you can try this for better control over blanks 

_Measure = 
VAR Result = CALCULATE (
    ROUND ( SUM ( AB00170_FACT_PBI[AMT] ) / 1000, 0 ),
    AB00170_FACT_PBI[Order day] <> "00"
)
RETURN
IF ( ISBLANK ( Result ), 0, Result )

Find this helpful? ✔ Give a Kudo • Mark as Solution – help others too!

[Surya31223122]

even we give few filter still we are getting below error ( using blank or coalase same error)

[johnt75]

You can add 0 to the result of the measure, like

My Measure =
CALCULATE (
    ROUND ( SUM ( AB00170_FACT_PBI[AMT] ) / 1000, 0 ),
    AB00170_FACT_PBI[Order day] <> "00"
) + 0

[Surya31223122]

Thanks,

even we give some filter below is the error