Measure including data from multiple table

[fpennisi17]

Hi everyone,

I'm using power PowerBI to evaluate the results of multiple forecast providers for a specific variable.

The model I created has the following tables:

- Fact table of the measured variable, with hourly timestamp, the asset on which the variable is measured and the value

- Fact table of the different forecast provider, with the hourly timestamp, the asset on which the variable is measured, the provider name and the forecasted value

- Time dimension hierarchy (tables with hour, date, month, etc)

- Asset dimension, with asset and related properties

- Forecast provider dimension, with the provider properties

I want to create a measure that calculates the AverageX of the difference between the forecast and measured value and divide that amount by a property of the asset.

The solution I found so far is to join, in the forecast fact table, the measured value by hour and asset and the asset property via power query, but I don't like this solution and I would like one that uses only DAX, while ensuring good performance.

I tried to use the CALCULATE function in conjuction with the RELATED one, but I wasn't able to find a valid solution.

Any suggestion would be strongly appreciated.

Thank you

[fpennisi17]

Could anyone help?