Nulls in Matrix Table with Column and Row Headers

[newpbiuser01]

Hello,

I have a matrix table that shows the Total by ID and Year. To get the Total - I have  the following measure:

Total =

Var Abc = Sum(Price)

Return

if(isblank(Abc), 0, Abc)

When I put this on the visual with the ID in the Row field, Year in the Column field and the Total in the Value field, the null handling does not work. They show up as blanks instead of $0. What I am doing wrong? I tried doing the following:

Total = Sum (Price) +0 with no luck. 

Also, none of these fields are from related tables - the year, ID and hte price are all from the same table. 

[v-nuoc-msft]

Hi @newpbiuser01

If the above code is available then I think you just need to change the filter criteria in the filter.

Based on the screenshot you provided, you need to calculate the sum of the prices under different countries and different years.

Please try to change the code to,

Measure = 
VAR abc = 
    CALCULATE(
        SUM('Table'[Price]), 
        FILTER(
            ALL('Table'), 
            'Table'[Country] = MAX('Table'[Country])
            &&
            'Table'[Year] = MAX('Table'[Year])
        )
    )
RETURN
IF(
    ISBLANK(abc), 
    0,
    abc
)

If you're still having problems, provide some dummy data. It is best presented in the form of a table.

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[newpbiuser01]

Hi @v-nuoc-msft ,

To clarify, if I have a dynamic matrix table with variable row/column fields depending on the value selected by the user, I cant just  use the All statement and specify each and every field selected right?  Main reason being, I can't possible know all combinations a user may use. If the user selects the Country and City for the Rows and Year and Month for the Column, the measure would be  

Measure =
VAR abc =
CALCULATE(
SUM('Table'[Price]),
FILTER(
ALL('Table'),
'Table'[Year] = MAX('Table'[Year])
&&
'Table'[Month] = MAX('Table'[Month])
&&
'Table'[Country] = MAX('Table'[Country])
&&
'Table'[City] = MAX('Table'[City])

)
)
RETURN
IF(
ISBLANK(abc),
0,
abc
)

But if they only select Country and ID, the code would need to be modified to say 'Table'[Country] = Max('Table'[Country]) && 'Table'[ID] = Max('Table[ID])

I can't hard code what field (country, year, id) the user will select. 

And depending on the data, I could have 10 different fields for rows and columns that a user could use. How do I handle the null handling for a dynamic matrix table (where a user can modify the column and row headers using the slicers?). 

The data table I'm using for this example is as follows:

YearIDCountryCityMonthPrice

2022	1	US	Boston	Jan	10
2023	2	Canada	Toronto	Feb	133
2024	3	US	New York	Mar	20
2022	4	Canada	Montreal	Apr	54
2023	1	Canada	Calgary	Jan	45
2024	2	Canada	Toronto	Feb	89
2022	3	US	Boston	Mar	42
2023	4	Canada	Toronto	Apr	100
2024	2	Canada	Vancouver	Jan	200
2023	4	US	Boston	Mar	78
2022	4	US	LA	May	96
2023	2	Canada	Toronto	Apr	100

Thank you. Really appreciate your help. 

[v-nuoc-msft]

Hi @newpbiuser01 

You cannot group dynamically based on user selections.

The filter function is designed to use only specific data in calculations.

FILTER function (DAX) - DAX | Microsoft Learn

Therefore, you can only specify the data to be grouped and cannot apply it dynamically.

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-nuoc-msft]

Hi @newpbiuser01 

For your question, here is the method I provided:

Here's some dummy data

"Table"

Create a measure.

Measure = 
VAR abc = 
    CALCULATE(
        SUM('Table'[Price]), 
        FILTER(
            ALL('Table'), 
            'Table'[ID] = MAX('Table'[ID])
            &&
            'Table'[Year] = MAX('Table'[Year])
        )
    )
RETURN
IF(
    ISBLANK(abc), 
    0,
    abc
)

Here is the result.

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[newpbiuser01]

Hi @v-nuoc-msft ,

Follow up question - this wouldn't work if I am using Field Parameters for my row and column selector and I can't use the row and column field in the calculate function. I apologize, I simplified my table in my original post, but I do have a table that uses field parameters so the row and column fields are variable. How do I show the null values as $0 in this case?

The measure here is 

Measure =

VAR abc =  SUM('Table'[Price])

RETURN

IF( ISBLANK(abc),  0, abc )

Thank you so much! I really appreciate any help.