Forum Discussion

LABrowne's avatar
LABrowne
Icon for Helper II rankHelper II
2 years ago
Solved

DAX: How to register blank values as zero

Hi there,

 

I am currently working on a different formulas within my table on Power BI.

 

CALCULATE(SUM(OrderTable[Amount]),OrderTable[OrderTypeId] = 22 || OrderTable[OrderTypeId] = 24, OrderTable, OrderTable[IsDeleted] = False, OrderStatus[OrderStatusId] <> 7)
 
This works in adding up certain types of orders, doesn't sum deleted orders and removes orders that were cancelled.
 
The problem is sometimes it is returning blank values which is correct but how can I say if there values/sum ISBLANK = $0.00?
 
Any advice would be great!
 
Kind regards,
Luke
  • Arpitb12's avatar
    Arpitb12
    2 years ago

    You can use the IF and ISBLANK functions in Power BI to conditionally display "$0.00" when the result is blank. 
    Result = VAR TotalAmount = CALCULATE( SUM(OrderTable[Amount]), OrderTable[OrderTypeId] = 22 || OrderTable[OrderTypeId] = 24, OrderTable[IsDeleted] = False, OrderStatus[OrderStatusId] <> 7 ) RETURN IF( ISBLANK(TotalAmount), "$0.00", TotalAmount )

3 Replies

  • Arpitb12's avatar
    Arpitb12
    Icon for Helper I rankHelper I
    2 years ago

    You can use the IF and ISBLANK functions in Power BI to conditionally display "$0.00" when the result is blank. 
    Result = VAR TotalAmount = CALCULATE( SUM(OrderTable[Amount]), OrderTable[OrderTypeId] = 22 || OrderTable[OrderTypeId] = 24, OrderTable[IsDeleted] = False, OrderStatus[OrderStatusId] <> 7 ) RETURN IF( ISBLANK(TotalAmount), "$0.00", TotalAmount )