Forum Discussion

newpbiuser01's avatar
newpbiuser01
Icon for Helper V rankHelper V
2 years ago

Nulls in Matrix Table with Column and Row Headers

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. 

 

 

 

Need Help

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      newpbiuser01
      Icon for Helper V rankHelper V
      2 years ago

      Hi Anonymous ,

       

      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. 
  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      newpbiuser01
      Icon for Helper V rankHelper V
      2 years ago

      Hi Anonymous ,

       

      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

      20221USBostonJan10
      20232CanadaTorontoFeb133
      20243USNew YorkMar20
      20224CanadaMontrealApr54
      20231CanadaCalgaryJan45
      20242CanadaTorontoFeb89
      20223USBostonMar42
      20234CanadaTorontoApr100
      20242CanadaVancouverJan200
      20234USBostonMar78
      20224USLAMay96
      20232CanadaTorontoApr100

       

      Thank you. Really appreciate your help. 

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        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.