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 of type Text. Consider using the VALUE or FORMAT function to convert one of the values.

 

The data being calculated is formatted as a Decimal Number. There are blanks in the data, so I think the error is being thrown over the first part of the OR. 


Any help would be greatly appreciated.

 

Exchange Rate = IF(OR('Stock Orders'[Exchange Rate For Report Estimates]="",'Stock Orders'[Exchange Rate For Report Estimates]=0),1,'Stock Orders'[Exchange Rate For Report Estimates])

 

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.

4 Replies

  • ValtteriN's avatar
    ValtteriN
    Community Champion
    3 years ago

    Hi,

    The issue is this part of your dax:

    'Stock Orders'[Exchange Rate For Report Estimates]=""

    Example and solution:

    This won't work:

    Measure 38 = IF(MAX('Table (21)'[Column1])="",1,0)
     

    This will work:

    Measure 38 = IF(ISBLANK(MAX('Table (21)'[Column1])),1,0)

    So in Short use ISBLANK to solve the issue.

     

    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!

    My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

     

  • 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.