topic Re: Blank matrix values change to zeros in DAX Commands and Tips

Blank matrix values change to zeros

009co — Fri, 16 Sep 2022 16:36:57 GMT

Trying to get my matrix blank values changed to zeros. Matrix looks like below screenshot.

I am doing IF ISBLANK calculation test on the calculation.

% Available =

IF (

ISBLANK(SUM('Table'[Available]) / SUM('Table'[Budget])),

SUM('Table'[Available]) / SUM('Table'[Budget])

)

If there is no result then it is blank, right? What am I missing? It doesn't return zeros, just stays blank.

tamerj1 — Fri, 16 Sep 2022 16:47:45 GMT

Hi 009co
The Project column and the Department column both must be independent dimension tables linked to the fact table through the respective columns. You can refer to my solution for this post just today Re: how to replace blank fields to zero in matrix ... - Microsoft Power BI Community

Moreover the measure can be written in more simple syntax as follows

% Available = COALESCE ( DIVIDE ( SUM ( 'Table'[Available] ), SUM ( 'Table'[Budget] ), 0 ), 0 )

009co — Fri, 16 Sep 2022 16:58:57 GMT

Project column and the Department columns are in same query as the values.

So yes it is obvious then that the blank values are because there are no rows in the query with those values for those dimensions.

Can't DAX replace these blanks with zeros without have a model with dimensions tables?

tamerj1 — Fri, 16 Sep 2022 17:08:17 GMT

009co
I don't think so