How to replace blank values by zero in matrix

[Anonymous]

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

measure=sum(amount)

final amount=if(isblank(measure),0,measure)

but it is unnecessarily displaying columns which has no data. is there any way so that we will have those columns only which has valid data and replace blank with zeros. 

[Mariusz]

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.

[Anonymous]

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]

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.

[Anonymous]

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