Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
joanna-g2
Frequent Visitor

Replacing null with zero brings extra rows to the matrix

I have a requirement to replace null with zero inside the matrix. I have two metrics and two dimensions and the data is at the monthly level. I have already replaced null with zero in the Power Query, but as seen below the values still appear as null.

joannag2_0-1645200610466.png


I have been trying a few approaches recommended in the Community:

- Adding +0 to the measure
- Use if(isblank(measure), 0, measure)

- Use COALESCE(measure, 0)


Each of those techniques replaces nulls with zeros, however, this is being done for all the values in the database. It is resulting in bringing many extra rows to my matrix along with very poor performance. Example below: 

joannag2_1-1645200766515.png

 

Is there any way to inject "0" in my matrix without bringing extra rows?

 

1 ACCEPTED SOLUTION
OwenAuger
Super User
Super User

Hi @joanna-g2 

 

You're facing a similar problem to the one discussed here.

 

The key thing do define is the conditions under which blank should be forced to zero, which may be dependent on the layout of the particular visual.

 

In your example, it looks like the condition for forcing zero to blank in a given cell of the matrix is:

  • "In both the current row and current column, at least one of Metric 1 and Metric 2 is nonblank."

I'm going to assume that a nonblank value for either of the metrics in a particular cell of the matrix implies a nonblank value when that metric is calculated in the context of the entire row or column. (If that's not true, it's a bit more complicated.)

 

With these assumptions, you could write measure like this (and similarly for Metric 2):

Metric 1 Zero = 
VAR RowNonEmpty =
    CALCULATE ( 
        NOT ISBLANK ( [Metric 1] ) || NOT ISBLANK ( [Metric 2] ),
        ALLSELECTED ( 'Date' )
    )
VAR ColumnNonEmpty =
    CALCULATE (
        NOT ISBLANK ( [Metric 1] ) || NOT ISBLANK ( [Metric 2] ),
        ALLSELECTED ( 'Dim 1' ),
        ALLSELECTED ( 'Dim 2' )
    )
VAR ForceZero =
    RowNonEmpty && ColumnNonEmpty
RETURN
    [Metric 1] + IF ( ForceZero, 0 )

ALLSELECTED is used to modify the filter context to the entire "row" or "column", which is dependent on which dimensions are included in the rows/columns.

The test of whether a row/column is nonempty then depends on the set of measures included in the visual.

 

Obviously, the code would change in a matrix with a different layout. I can't think of any generic solution that doesn't depend on visual layout.

 

Given the common logic, you could also use a Calculation Group to capture the logic in a Calculation Item and avoid modifying multiple measures.

 

Example PBIX attached.

Regards,

Owen


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

4 REPLIES 4
joanna-g2
Frequent Visitor

Hi @OwenAuger ,

 

Thanks, this sollution seems to be what I was looking for!

However, I'm experiencing a challange with the RowNonEmpty variable. In one of the matrixes I use quarter instead of month. When I use Quarter_Name in a matrix and change Metric 1 Zero formula to ALLSELECTED ( D_Quarter.[Quarter_Name] ) I'm still getting banks instead of 0s. However, if I use Quarter_ID - ALLSELECTED ( D_Quarter.[Quarter_ID] ) the nulls are correctly replaced by the 0s. Any idea why would that happen?

Thanks a lot!

Joanna

Hi Joanna,

That's good to hear 🙂

 

By any chance is Quarter_Name is set to sort by Quarter_ID?

 

In general, the "sort by" column is included in the DAX query whenever the primary column is present, even though it is not displayed in the visual. This means any modifications of filters on the primary column also need to be applied to the "sort by" column.

 

To handle this, I would suggest either:

  1. Include both columns as arguments within ALLSELECTED:
    ALLSELECTED ( D_Quarter.[Quarter_Name], D_Quarter.[Quarter_ID] )
  2. Instead provide the table reference as the argument of ALLSELECTED, to cater for any columns of that table that might be included:
    ALLSELECTED ( D_Quarter.)

Do either of those fix it?

Regards,

Owen

 


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

Hey @OwenAuger 

Indeed, including the whole D_Quarter table did the trick. 

Also, nulls get correcltly replaced without introdcuing VAR ColumnNonEmpty. Only checking for empty rows was enough.

Thanks again for help.

OwenAuger
Super User
Super User

Hi @joanna-g2 

 

You're facing a similar problem to the one discussed here.

 

The key thing do define is the conditions under which blank should be forced to zero, which may be dependent on the layout of the particular visual.

 

In your example, it looks like the condition for forcing zero to blank in a given cell of the matrix is:

  • "In both the current row and current column, at least one of Metric 1 and Metric 2 is nonblank."

I'm going to assume that a nonblank value for either of the metrics in a particular cell of the matrix implies a nonblank value when that metric is calculated in the context of the entire row or column. (If that's not true, it's a bit more complicated.)

 

With these assumptions, you could write measure like this (and similarly for Metric 2):

Metric 1 Zero = 
VAR RowNonEmpty =
    CALCULATE ( 
        NOT ISBLANK ( [Metric 1] ) || NOT ISBLANK ( [Metric 2] ),
        ALLSELECTED ( 'Date' )
    )
VAR ColumnNonEmpty =
    CALCULATE (
        NOT ISBLANK ( [Metric 1] ) || NOT ISBLANK ( [Metric 2] ),
        ALLSELECTED ( 'Dim 1' ),
        ALLSELECTED ( 'Dim 2' )
    )
VAR ForceZero =
    RowNonEmpty && ColumnNonEmpty
RETURN
    [Metric 1] + IF ( ForceZero, 0 )

ALLSELECTED is used to modify the filter context to the entire "row" or "column", which is dependent on which dimensions are included in the rows/columns.

The test of whether a row/column is nonempty then depends on the set of measures included in the visual.

 

Obviously, the code would change in a matrix with a different layout. I can't think of any generic solution that doesn't depend on visual layout.

 

Given the common logic, you could also use a Calculation Group to capture the logic in a Calculation Item and avoid modifying multiple measures.

 

Example PBIX attached.

Regards,

Owen


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors