Forum Discussion

BeastHouse's avatar
BeastHouse
Frequent Visitor
3 years ago
Solved

IF/OR Formula Help

Hi, trying to calculate an exchange rate used in a procurement data set. The below formula is throwing the error DAX comparison operations do not support comparing values of type Number with values o...
blanks
if
OR
values
  • FreemanZ's avatar
    FreemanZ
    3 years ago

    hi BeastHouse 

    try like:

    Exchange Rate =
    IF(
    'Stock Orders'[Exchange Rate For Report Estimates]=0,
    1,
    'Stock Orders'[Exchange Rate For Report Estimates]
    )
     
    DAX take 0 and blank as the same.
FreemanZ's avatar
FreemanZ
Super User
3 years ago

hi BeastHouse 

try like:

Exchange Rate =
IF(
'Stock Orders'[Exchange Rate For Report Estimates]=0,
1,
'Stock Orders'[Exchange Rate For Report Estimates]
)
 
DAX take 0 and blank as the same.
  • BeastHouse's avatar
    BeastHouse
    Frequent Visitor
    3 years ago

    I think this could be the simple way of going about it! I am trying to transfer from Excel to Power Bi, so struggle with translating some of the formulas in the existing database. Thanks a lot.

  • BeastHouse's avatar
    BeastHouse
    Frequent Visitor
    3 years ago

    I also have this one, similar situation. 

    From Excel:

     

    =IF(AND([@[PO/Credit Check]]="TRUE",[@[Invoice Finalised]]=FALSE),([@[Quoted Amount]]/[@[Exchange Rate]])-[@[Invoice Amount]],0)

     

    I tried the following in Power BI

    PO Outstanding = IF('Stock Orders'[PO/Credit Check]="TRUE"&&'Stock Orders'[Invoice Finalised]="FALSE",'Stock Orders'[Quoted Amount]/'Stock Orders'[Exchange Rate]-'Stock Orders'[Invoice Amount],0)

     

    But still get DAX comparison operations do not support comparing values of type True/False with values of type Text. Consider using the VALUE or FORMAT function to convert one of the values. I have also tried with an AND statement but same result.