Forum Discussion

mahsy's avatar
mahsy
Helper I
4 years ago
Solved

Replace null by 0 in matrix

Hi,

 

I need your help please !

 

This is what i want :

 

This is what i get :

I would like to display all values and replace null by 0.

 

Thank you for your help !

 

replacenullvalueinmatrix
  • VahidDM's avatar
    VahidDM
    4 years ago

    Hi mahsy 

     

    Try this measure:

    Sum_Measure =
VAR _A =
    ADDCOLUMNS (
        CROSSJOIN ( VALUES ( 'Table'[Axel] ), VALUES ( 'Table'[AX2] ) ),
        "M1",
            CALCULATE (
                SUM ( 'Table'[Measure] ),
                FILTER (
                    'Table',
                    'Table'[Axel] = EARLIER ( [Axel] )
                        && 'Table'[AX2] = EARLIER ( [AX2] )
                )
            ) + 0
    )
VAR _B =
    SUMX ( _A, [M1] )
RETURN
    _B

     

    the output:

     

     

    If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
    Appreciate your Kudos!!
    LinkedIn:     www.linkedin.com/in/vahid-dm/

     

     

3 Replies

  • VahidDM's avatar
    VahidDM
    Super User
    4 years ago

    Hi mahsy 

     

    Try this measure:

    Sum_Measure =
VAR _A =
    ADDCOLUMNS (
        CROSSJOIN ( VALUES ( 'Table'[Axel] ), VALUES ( 'Table'[AX2] ) ),
        "M1",
            CALCULATE (
                SUM ( 'Table'[Measure] ),
                FILTER (
                    'Table',
                    'Table'[Axel] = EARLIER ( [Axel] )
                        && 'Table'[AX2] = EARLIER ( [AX2] )
                )
            ) + 0
    )
VAR _B =
    SUMX ( _A, [M1] )
RETURN
    _B

     

    the output:

     

     

    If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
    Appreciate your Kudos!!
    LinkedIn:     www.linkedin.com/in/vahid-dm/

     

     

  • ValtteriN's avatar
    ValtteriN
    Community Champion
    4 years ago

    Hi,

    You can use IF + ISBLANK to achieve this. Example:
    DAX: 

    NullToZero = IF(ISBLANK(SUM(NullToZero[Data])),0,SUM(NullToZero[Data]))

     


    I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

    • mahsy's avatar
      mahsy
      Helper I
      4 years ago

      Hi,

       

      I get this 

      I would like to get 0 in empty cells. Thank for your help !