Replacing NIL values in rows with zero - matrix table

DAX DAX Calculations
1

Hi eDNA Friends,

Hi everyone, how can I replace the rows in Matrix Table having blank/nil values with zero - which is currently greyed out? The + 0 old trick doesn’t seem to work. Thanks in advance :slightly_smiling_face:

image

Here are the measures:

        P&L New = 
(
    CALCULATE (
        SUM ( 'Invoices'[Line Amount FX Calculation] ),
        FILTER (
            'Accounts',
            Accounts[Class] = "Revenue"
                || Accounts[Class] = "Expense"
        ), USERELATIONSHIP( Invoices[Invoice Line ID], 'Tracking Category CONNECTIONS'[ID])
    )
) - [P&L - Credit Notes] + [P&L (Journals) New]

P&L - Credit Notes = 
CALCULATE (
    SUM ( 'Credit Notes'[Line Amount Credit Note Calculation FX] ),
    FILTER (
        'Accounts',
        'Accounts'[Class] = "Revenue"
            || Accounts[Class] = "Expense"
    ), USERELATIONSHIP( 'Credit Notes'[Credit Note Line ID], 'Tracking Category CONNECTIONS'[ID])
)

P&L (Journals) New = 
CALCULATE ( -1* ( SUM ( 'Journals'[Net Amount FX] ) ),
    FILTER ( 'Journals', 'Journals'[Split] = "JOURNALS" ),
    FILTER (
        'Accounts',
        'Accounts'[Class] = "Revenue"
            || Accounts[Class] = "Expense"
    ), USERELATIONSHIP( Journals[ID], 'Tracking Category CONNECTIONS'[ID])
)
2

Hi @ronald_balza,

Try COALESCE
That returns the first argument that does not evaluate to a blank value.

for example:
COALESCE( [YourMeasure], 0 )

3

Hello @ronald_balza,

Thank You for posting your query onto the Forum.

Can you please try the below provided measures to replace blanks with the 0?

P&L New = 
VAR _P_and_L_New = 
(
    CALCULATE (
        SUM ( 'Invoices'[Line Amount FX Calculation] ),
        FILTER (
            'Accounts',
            Accounts[Class] = "Revenue"
                || Accounts[Class] = "Expense"
        ), USERELATIONSHIP( Invoices[Invoice Line ID], 'Tracking Category CONNECTIONS'[ID])
    )
) - [P&L - Credit Notes] + [P&L (Journals) New]

RETURN
IF( ISBLANK( _P_and_L_New ) , 
    0 , 
    _P_and_L_New )




P&L - Credit Notes = 
VAR _P_and_L_Credit_Notes = 
CALCULATE (
    SUM ( 'Credit Notes'[Line Amount Credit Note Calculation FX] ),
    FILTER (
        'Accounts',
        'Accounts'[Class] = "Revenue"
            || Accounts[Class] = "Expense"
    ), USERELATIONSHIP( 'Credit Notes'[Credit Note Line ID], 'Tracking Category CONNECTIONS'[ID])
)

RETURN
IF( ISBLANK( _P_and_L_Credit_Notes ) , 
    0 , 
    _P_and_L_Credit_Notes )




P&L (Journals) New = 
VAR _P_and_L_Journals_New = 
CALCULATE ( -1* ( SUM ( 'Journals'[Net Amount FX] ) ),
    FILTER ( 'Journals', 'Journals'[Split] = "JOURNALS" ),
    FILTER (
        'Accounts',
        'Accounts'[Class] = "Revenue"
            || Accounts[Class] = "Expense"
    ), USERELATIONSHIP( Journals[ID], 'Tracking Category CONNECTIONS'[ID])
) 

RETURN
IF( ISBLANK( _P_and_L_Journals_New ) , 
    0 , 
    _P_and_L_Journals_New )

Hoping you find this useful and meets your requirements that you’ve been looking for. :slightly_smiling_face:

Thanks and Warm Regards,
Harsh

4

Hi @Melissa , thanks for taking the time on this. It does replaced the blank with zero, however, there were columns that appeared with zero values in it as you can see on the snapshot below. Thanks again!
image

5

Hi @Harsh , thanks for taking the time on this. It does replaced the blank with zeroes, however, columns appears with zero values as shown on the snapshot. Thanks again!

image
image748×385 14.2 KB

6

Hello @ronald_balza,

When you use this type of technique which is suggested by me and @Melissa the columns which were blanks earlier in it’s entirety will also come-up or start showing. So now, what you can simply do is drag or adjust the width of the columns and hide them which are un-necessary in your case.

Thanks and Warm Regards,
Harsh

1 Like
7

Alternatively, if you don’t want to “hide” columns, you can add another conditional test with an IF statement. For example let’s say in variable1 you determine (calculate) if a blank is returned at ‘column total level’ and in variable2 you perform your original calculation.
Now when the VAR1 returns a blank you want VAR2 else you want VAR2 +0, or inside COALESE or with an IF ISBLANK statement to return a 0 instead.

I hope this is helpful

2 Likes
8

@Harsh , thanks for taking the time on this. Much appreciated. Yeah you are right, because if I hide the columns then If there’s a value in it (since it is a moving data) then there’s no point in hiding it.

9

Hi @Melissa , thanks for taking the time on this. Really appreciated it. As per my understanding on your suggestion, it would be look like this? Still doing the same (appears the column with zero)

        P&L (No Blanks) = 
               CALCULATE( IF( [P&L New] = BLANK(), 0, COALESCE([P&L New],0)))

image

10

Hi @ronald_balza,

Not quite, no. The pattern is more like this (used a simple example because you didn’t provide a sample PBIX):

Example (no blanks) =
VAR CurrentCalc =
    SUM( [Quantity] )
VAR ColTotalCalc =
    SUMX( [Quantity], ALL( table ) )
VAR Result =
    IF( ISBLANK( ColTotalCalc ), CurrentCalc, CurrentCalc + 0 )
RETURN
    Result

Replace with the following:
CurrentCalc is your original measure that also returned blanks
ColTotalCalc is a calculation at subtotal- or grandtotal level, whatever makes sense in your case. Note that you can expand this pattern to each subtotal level by creating more variables, if that’s required.

I hope this is helpful.

1 Like
11

Hi @Harsh , @Melissa - thank you both for helping me out on this one. Really appreciated it. After spending time with it, figured out how to do this by using this measure.

P&L New = 
VAR _ColSubTotal =
    CALCULATE ( [P&L], ALL ( Accounts ) )
VAR _Result =
    IF ( _ColSubTotal <> 0, [P&L] + 0 )
RETURN
    _Result