Struggling to 'left join' my data model to budget figures

[Nemza]

Hi, 

I'm coming from a SQL backgroud to "left join" is the best way I can describe this issue. 

I have a data model that, with a bit of help from the community, is working as expected. It is showing me the actual time spent by a user per day on various tasks. All the tables are related nicely and they all seem to speak to each other. I will refer to this as the "actual time" or "actual data"

My problem is that I would like to introduce budget a.k.a forecast data to the model. 

This is data that sits outside the data model - I've captured it on an excel spreadsheet and want to incorporate it into my model. 

The first thing to note is that the actual data's level of granularity is daily.

The forecast data that I am introducing is aggregated per month. 

Here is a screen grab of my data model with the fields i'd like to join highlighted*

My data model

 *When i've highlighted a table name its becuase the field is not displayed in the model view, but rest assured the field is there and it's named the same as the forecast table field.

If you would like to get a pbix file from me, I can create one, but I think the image of the data model will suffice for what I am asking.

The actual data has the following fields that I woudl like to 'join' the forecast data with:

• Date.First of Month (I'm sure sure what to actually join to here)
• Project.Name
• User.Name
• Client.Name

The forecast data has the following fields that I would like to 'join' to the actual data:

• First of Month (a date where the day is always 01 e.g. 2020/03/01, 2020/04/01 etc...)
• Project
• User
• Client

The end goal being that I would like to join the data and show the user things like:

• Actual allocation per user, project, client, month vs. forecasted allocation 
• Actual rate per user, project, client, month vs. forecasted rate
• Etc...

I'd really appreciate any help I can get on this. 

Thus far I have tried to create dim tables that act as bridging tables and I've tried to use the TREATAS function but to no avail.

Thanks in advance!!

[v-yingjl · Accepted solution]

Hi @Nemza ,

You can refer these articles about TREATAS() function:

1. https://www.sqlbi.com/articles/propagate-filters-using-treatas-in-dax/
2. https://www.mssqltips.com/sqlservertip/5482/how-to-use-the-treatas-function-in-dax/

You can also share your .pbix file here for further discussion.

Best Regards,

Yingjie Li

[Nemza]

Hi @lbendlin , 

I've looked at many tuts on TREATAS but the tuts are very simple and I can't get it to work on many colums. 

@v-yingjl thanks for the resources, I looked through them and I ended up coming right. 

What I was looking for was something like this:

Rate CCY Target per Month Client Project User = CALCULATE(
 AVERAGE(ForecastTargets[Rate CCY]),
 TREATAS(VALUES('Date'[First of Month]),ForecastTargets[First of Month]),
 TREATAS(VALUES(User[Name]),ForecastTargets[User]),
 TREATAS(VALUES(Client[Name]),ForecastTargets[Client]),
 TREATAS(VALUES(Project[Name]),ForecastTargets[Project])
)

So, under the filter part of the CALCULATE function I can just list many TREATAS lines; one per field that I am joining on. 

Thanks!

[lbendlin]

TREATAS () should work. What issues have you encountered?