Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

How to replace blank values by zero in matrix

I have one column amount. and i am adding measure sum(amount) in matrix. but for some row column combination it is displaying blank so i want to replace those blank with zeros. I tried measure meas...
Need Help
Show and Tell
Mariusz's avatar
Mariusz
Community Champion
6 years ago

Hi Anonymous 

 

Try 

 SUM( table[amount] ) + 0

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
Mariusz Repczynski



  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    adding zero in measure also leads to same result which i have shared. i want same functionality of power BI matrix visula which don't show unnecessary columns even if they are in range.

    I want to remove those columns has total 0(if i have not replaced blank with zeros i am getting expect result but our clients want zero instead of blanks).

    • Mariusz's avatar
      Mariusz
      Community Champion
      6 years ago

      Hi Anonymous 

       

      Try something like...

      IF(  
    CALCULATE( COUNTROWS( YourTable ), ALLEXCEPT( YourTable, 'DateTable' ) ) > 0,
    [YourMeasure] + 0
)

       

      Best Regards,
      Mariusz

      If this post helps, then please consider Accepting it as the solution.

      Please feel free to connect with me.
      Mariusz Repczynski



      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        above measure is syntactically wrong.

         

        i have modified it

        total = IF( 
            CALCULATE( COUNTROWS('Fact Claim Lag Aggregate'), ALLEXCEPT('Lag Aggregate Paid Date','Lag Aggregate Paid Date'[Lag Aggregate Paid Date] ) ) > 0,
            ([Lag Aggregate Total Paid Original Amount] + 0),[Lag Aggregate Total Paid Original Amount]
        )
         
        please check and let me know, is it as per your logic?
         
         
        Fact lag table
        lag key
        paid date key
        service date key
        original amount
         
         
        lag aggregate paid date(dimension)
        paid date key
        paid date
        month year
         
        service date(dimention)
        service date key
        service date
        month year
         
        both dimention are connected with fact