Forum Discussion
Helper IIIjoin a virtual table with an imported table
Hi Guys,
Thanks all that take the time to answer questions on the community.
Here is my problem I created a virtual table ( 'forecasttable') that contains a forecast for the current month. I want to create a relationship between this table and my calendar. I have tried both join functions but it doesnt find the joining columns.
Here is the working code that returns the total forecasted amount.
VAR forecasttable=
ADDCOLUMNS(
uniondatesandgroups,
"forecast",
IF( [Date] <= lastdate,
[$ Total],
[dailyforecast]
)
)
var forecast =
SUMX( forecasttable, [forecast] )
RETURN
forecast
What you have there is a measure which returns a scalar value. The virtual table is only relevant to the calculation and is not a physical table in the model. So you can't create relationships.
You need to create a new physical table using the code you have for the virtual table in a new table (under "Modeling" in the ribbon).
4 Replies
PaulDBrownCommunity Champion
Can you share a sample PBIX file through OneDrive, Google Drive, Dropbox....?
- VahidDM
Super User
Please see this link and share more details about your question here to be able to work on it.
Appreciate your Kudos!
- PaulDBrown
What you have there is a measure which returns a scalar value. The virtual table is only relevant to the calculation and is not a physical table in the model. So you can't create relationships.
You need to create a new physical table using the code you have for the virtual table in a new table (under "Modeling" in the ribbon).
reynaldo_malaveThanks PaulDBrown, this is the answer I was looking for. I did create a calculated table but its fixed table, so no slicing and dicing since it only returns totals.
