Adding calculated column to date table

[StanRuigrok]

Hi,

I've got a dimension date table with a dateid (yyyymmdd) and a currency table with fields: dateid, currency, value. 
I want to add 4 columns to the date table, 1 for each currency with the value on that date. 

Because of later calculations with the values on row level I want calculated columns instead of measures. 

The catch, the date table is in the original model, and the currency table is in a semantic model. 

Everytime I try something with a calculated column I get the next error:
An unexpected error occurred (file 'tmmdmodeltm.cpp', line 11112, function 'MDModelTM::CreateHierIterRequestImpl').

Looks like the formula's I try aren't available in this scenario. 

The columns are connected via DateId in both tables. 

[Ilgar_Zarbali · Accepted solution]

This won’t work while your Date table is local and the Currency table comes from a Power BI semantic model (DirectQuery for PBI datasets/AAS).
In that setup, calculated columns (and calculated tables) can’t reference columns from a remote/semantic-model table. Only measures can cross that boundary at query time. The cryptic error you’re seeing is the symptom of that limitation.

What you can do instead

1)

Bring Currency local (recommended if you truly need row-level columns)
Import the currency data into the same model (from the original source or a dataflow).
Once both tables are local, add 1 column per currency:

USD :=
VAR id = 'Date'[DateId]
RETURN
CALCULATE (
MAX ( 'Currency'[Value] ),
'Currency'[DateId] = id,
'Currency'[Currency] = "USD"
)

Repeat for EUR/GBP/etc. (wrap with COALESCE(…, 0) if you need blanks as zeros).

2)

Do it in Power Query (materialize at refresh)
If you can reach the currency source in Power Query, merge Date ↔ Currency on DateId and expand the needed columns (or pivot Currency to wide shape: one column per code). This gives you physical columns without DAX.

3)

Stay remote and use measures (no row columns)
If moving the table isn’t possible, create measures instead of columns, e.g.:

USD :=
CALCULATE (
MAX ( 'Currency'[Value] ),
'Currency'[Currency] = "USD"
)

These will work across the relationship, but you won’t have row-level calculated columns for further calculated-column logic.

Calculated columns can’t pull from a remote semantic model table. To get 4 currency columns on the Date table, either import the Currency table into the same model (or via PQ/dataflow) and then add the DAX columns, or materialize the join in Power Query. Measures are the only cross-model option if you must keep Currency remote.

I hope it will help. If so, please give kudoes and accept it as a solution.

[v-venuppu]

Hi @StanRuigrok ,

May I ask if you have resolved this issue? Please let us know if you have any further issues, we are happy to help.

Thank you.

[v-venuppu]

Hi @StanRuigrok ,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @Kedar_Pande @Ilgar_Zarbali @danextian @Nadeera for the prompt response.

I wanted to check if you had the opportunity to review the information provided and resolve the issue..?Please let us know if you need any further assistance.We are happy to help.

Thank you.

[Kedar_Pande · Accepted solution]

You can't create calculated columns that reference tables from different semantic models. This is a hard limitation in Power BI.

The only solution is to merge the currency values into your date table during data transformation using Power Query, before the data loads into the model.

[Ilgar_Zarbali · Accepted solution]

This won’t work while your Date table is local and the Currency table comes from a Power BI semantic model (DirectQuery for PBI datasets/AAS).
In that setup, calculated columns (and calculated tables) can’t reference columns from a remote/semantic-model table. Only measures can cross that boundary at query time. The cryptic error you’re seeing is the symptom of that limitation.

What you can do instead

1)

Bring Currency local (recommended if you truly need row-level columns)
Import the currency data into the same model (from the original source or a dataflow).
Once both tables are local, add 1 column per currency:

USD :=
VAR id = 'Date'[DateId]
RETURN
CALCULATE (
MAX ( 'Currency'[Value] ),
'Currency'[DateId] = id,
'Currency'[Currency] = "USD"
)

Repeat for EUR/GBP/etc. (wrap with COALESCE(…, 0) if you need blanks as zeros).

2)

Do it in Power Query (materialize at refresh)
If you can reach the currency source in Power Query, merge Date ↔ Currency on DateId and expand the needed columns (or pivot Currency to wide shape: one column per code). This gives you physical columns without DAX.

3)

Stay remote and use measures (no row columns)
If moving the table isn’t possible, create measures instead of columns, e.g.:

USD :=
CALCULATE (
MAX ( 'Currency'[Value] ),
'Currency'[Currency] = "USD"
)

These will work across the relationship, but you won’t have row-level calculated columns for further calculated-column logic.

Calculated columns can’t pull from a remote semantic model table. To get 4 currency columns on the Date table, either import the Currency table into the same model (or via PQ/dataflow) and then add the DAX columns, or materialize the join in Power Query. Measures are the only cross-model option if you must keep Currency remote.

I hope it will help. If so, please give kudoes and accept it as a solution.

[danextian]

Hi @StanRuigrok 

Why would you add those currency related columns to the dates table when you could just create a relationship? And assuming there are more than one currencies for each date, a calculated column won't allow you to bring all those rows in separately. 

[StanRuigrok]

Hi Dane,

The currency table is build up the following way:
date - currency - value. 

Currnecy table has indeed with multiple currencies which means dateid's aren't unique. There is a third (fact) table, which contains multiple dateid's as well. 

If I connect those by dateid, the cardinality will be many - many. 

So there are 2 possible situations: 

Invoice table (>1 dates)  -> Date (unique) -> Currency (>1 dates)
Date (unique) <- Invoice table (>1 dates) - > currency (>1dates).

The invoice table contains currency as well as amount. I want to multiply the amount by the value. 
I thought if I create a date table with 4 extra columns, 1 for each currency and value on that date, the multiplying will be easy, but because 1 is table is from the standard model and the other from the semantic model I run into trouble. 

[Nadeera]

Hi. Calculated columns cannot reference tables from a different models. Only measure can do it.

You have two options

1. if you are using Power BI desktop, use Power query to merge those columns. 

2. You need to bring currency table to same model as Date table (Original model as you mentioned) . then you can create calculate column using LOOKUPVALUE formula.

See the example formula :

USD_Value = LOOKUPVALUE(

  Currency[Value],

  Currency[DateId], Date[DateId],

  Currency[Currency], "USD"

)

 

If you got answer from this, please mark as the solution and give Kudos.

 

Thank you.

Have a nice day !

[bhanu_gautam]

@StanRuigrok , Try using

DAX
CurrencyUSD =
CALCULATE(
SELECTEDVALUE(CurrencyTable[Value]),
CurrencyTable[Currency] = "USD",
CurrencyTable[DateId] = DateTable[DateId]
)