Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

"If" statement with not exists

Hi all,

 

Here is my situation:

I'm trying to get the currency exchange rate for each day into the sales table.

The currency exchange table has the fields "date_effective", "from currency id", "to currency id" and "exchange rate". It has two inactive relationships with the sales table via "dates" and "from currency id" and I always want the conversion to USD (currency id = 1).

I was using the formula below to get the rate and it worked well except that the currency exchange table does not have one day listed (system issues). For example 9/1/2020 does not exist on that table and when I use the formula below the result for that day comes out as blank. I wanted to include a condition on my formula below which will use the currency exchange rate from the day before in that case. Can anyone please help?

 

DAX:

Exchange rate in USD = CALCULATE ( FIRSTNONBLANK ( Currencies[EXCHANGE_RATE], 1 ), FILTER ( Currencies, Currencies[DATE_EFFECTIVE] = 'Sales '[trandate] && Currencies[from currency] = 'Sales Transactions'[currency id] && Currencies[to currency] = 1 ) )

 

Data sample:

Currency exchange rate table 

from currencyto currencyDATE_EFFECTIVEEXCHANGE_RATE
319/17/20201.3205
129/17/20201.29753
139/17/20200.757289
149/17/20201.17994
169/17/20200.013588

 

Sales transactions (Last column is my dax):

tranidtrandate Revenue CURRENCY_IDExchange rate in USD - DAX
id19/21/2020 $     5.0040.834676
id29/29/2020 $     2.0040.855198
id39/21/2020 $     3.0040.848565
id69/1/2020 $     6.004 
id79/21/2020 $     7.0040.834676
id89/29/2020 $     8.0040.855198
id99/1/2020 $     9.004 

 

 

Thanks!!

 

2 REPLIES 2
v-diye-msft
Community Support
Community Support

Hi @Anonymous 

 

Not really enough information to go on, you might consider providing your dummy pbix that would be helpful for us to investigate it further. 

You can upload it to the onedrive for business and share the link here. please don't forget to disclose the expected results and remove the confidential info.

 

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.
lbendlin
Super User
Super User

You can choose  IF(ISBLANK()) or COALESCE() to handle these situations.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.