Calculate with filters from multiple columns

[Micgig]

Hi everyone, 

I was trying a formula of the following type (which doesn't work). What I'd like it to do is: if the business date (SALES[Business_date]) is equal to the value date (CURRENCY[Value_date]) the formula should multiply the sales amount (SALES[Amount]) by the exchange rate (CURRENCY[Exchange_rate]), given that the currency code on the Sales table (Sales[Currency_code]) is equal to the currency code on the currency table (CURRENCY[quote_cur]).

The formula should look like the following:

Formula = IF(SALES[Business_Date]=CURRENCY[Value_date], CALCULATE(SALES[Amount]*CURRENCY[Exchange_rate], SALES[Currency_code] = CURRENCY[quote_cur],0))

The error message i get says: "the expression contains columns from multiple tables, but only columns from a single table can be used in a True/False expression that is used as a table filter expression".

How else could I execute the above in your opinion?

thank you so much

[Greg_Deckler]

@Micgig Maybe:

Column in Sales Table =
  VAR __BusinessDate = 'Sales'[Business Date]
  VAR __CurrencyCode = 'Sales'[Currency_code]
  VAR __CurrencyRate = 
    MAXX(
      FILTER(
        'Currency',
        [Value_date]=__BusinessDate && [quote_cur]=__CurrencyCode
      ),
      [Exchange_rate]
    )
RETURN
  IF(
    NOT(ISBLANK(__CurrencyRate)),
    'Sales'[Amount] * __CurrencyRate,
    'Sales'[Amount]
  )

[Micgig]

@Greg_Deckler Thanks a lot Greg for the prompt reply and I really appreciate your effort!

I think there is still an issue though as I receive the following error message:

"A single value for column 'Currency_code' in table 'Sales' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count or sum to get a single result".

Infact that column doesn't contain unique values because currency codes (USD, EUR, CNY, ...) repeat themselves thousands of times.

Anyway I think the solution was almost complete, probably only needs to be amended.

thanks!

Michele

[Greg_Deckler]

@Micgig Sounds like you are trying to use a formula specifically designed as a calculated column formula in a measure. This is why I specified "Column" in the name of the formula to indicate it was designed for a calculated column. As a measure, you would need something like:

Column in Sales Table =
  VAR __BusinessDate = MAX('Sales'[Business Date])
  VAR __CurrencyCode = MAX('Sales'[Currency_code])
  VAR __CurrencyRate = 
    MAXX(
      FILTER(
        'Currency',
        [Value_date]=__BusinessDate && [quote_cur]=__CurrencyCode
      ),
      [Exchange_rate]
    )
RETURN
  IF(
    NOT(ISBLANK(__CurrencyRate)),
    'Sales'[Amount] * __CurrencyRate,
    'Sales'[Amount]
  )

It's really tough to say though because I made a ridiculous amount of assumptions given the paucity of information provided.

Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[Micgig]

Thanks @Greg_Deckler Greg!

To provide the additional details you requested.

1. Sample data as text, use the table tool in the editing bar.

I removed many unnecessary columns to simplify.

SALES

fields data type:

business_date --> Date

Currency_code --> Text

Amount --> Decimal number

Converted amount --> Decimal number

Business_date	Currency_code	Amount	Converted amount
2017-01-05	GBP	100	?
2017-01-03	GBP	100	?

CURRENCY

fields data type:

base_cur --> Text

quote_cur --> Text

value-date--> Date

Exchange_rate --> Decimal number

Base_cur	Quote_cur	Value_date	Exchange_rate
EUR	GBP	2017-01-05	1.1
EUR	GBP	2017-01-03	1.1

2. Expected output from sample data

It should be the calculation of the "Converted amount" column in the SALES table as follows.

Business_date	Currency_code	Amount	Converted amount
2017-01-05	GBP	100	110
2017-01-03	GBP	100	110

3. Explanation in words of how to get from 1. to 2.

Applying the exchange rate from CURRENCY table to the amount in the SALES table, provided that the exchange rate used is the one published for the date where the sales occurs (SALES[business_date]=CURRENCY[Value_date]) and that the currency of the sales amount matches the quotation currency in the CURRENCY table (SALES[Currency_code]=CURRENCY[Quote_cur]).

My final goal should actually to convert all Sales Amounts row by row from whatever currency they are listed in (GBP, USD, CNY, ...) to EUR.

Please let me know if it could be helpful to add further details!

Thanks!

[Greg_Deckler]

@Micgig Very helpful. Here is a column in your "Sales" table that should do the trick:

Column = 
    VAR __Table = 'Table33a'
    VAR __BusinessDate = [Business_date]
    VAR __CurrencyCode = [Currency_code]
RETURN
    [Amount ] * MAXX(FILTER(__Table,[Business_date] = __BusinessDate && [Quote_cur] = __CurrencyCode),[Exchange_rate])

[Micgig]

Thanks @Greg_Deckler !

Unfortunately there is still some issue.

I suppose 'Table33a' is a typo (I tried it and get error message "Cannot find table 'Tablea33a').

However, even when I modify it with 'F_SALE_FLASH' the formula still doesn't work.

Column =

VAR __Table = 'F_SALE_FLASH'

VAR __BusinessDate = [BUSINESS_DATE]

VAR __CurrencyCode = [CURRENCY_CODE]

RETURN

[AMT_DISCOUNTED ] * MAXX(FILTER(__Table,[Business_date] = __BusinessDate && [Quote_cur] = __CurrencyCode),[Exchange_rate])

Somehow PowerBi doesn't accept the fields [BUSINESS_DATE] and [CURRENCY_CODE], which are respectively a date and text (infact they are shown in grey color in the screenshot below).

What could be the issue here in your opinion?

Lastly, the formula doesn't seem to include the mentioning of the "CURRENCY" table anywhere, is this correct, considering that the fields [Quote_cur] and [Exchange_rate] come from there?

Sorry for the continuous hassle, hope I haven't disturbed you too much.

Thanks a lot!

Wish you a good day

[Greg_Deckler · Accepted solution]

@Micgig Not a typo, just the table in my model. The code I provided was for a column in the table, not a measure. If you need a measure you will need to wrap something like a MAX around your column references. Attaching PBIX below sig.