topic Re: IF/OR Formula Help in DAX Commands and Tips

IF/OR Formula Help

BeastHouse — Mon, 06 Feb 2023 09:48:25 GMT

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])

Re: IF/OR Formula Help

ValtteriN — Mon, 06 Feb 2023 10:48:16 GMT

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/

Re: IF/OR Formula Help

FreemanZ — Mon, 06 Feb 2023 10:48:56 GMT

hi BeastHouse

try like:

Exchange Rate =

IF(

'Stock Orders'[Exchange Rate For Report Estimates]=0,

'Stock Orders'[Exchange Rate For Report Estimates]

)

DAX take 0 and blank as the same.

Re: IF/OR Formula Help

BeastHouse — Mon, 06 Feb 2023 10:51:57 GMT

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.

Re: IF/OR Formula Help

BeastHouse — Mon, 06 Feb 2023 11:18:51 GMT

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.