Colleague capacity and forecast in two different queries

[Anonymous]

Hello guys,

I faced a problem while building new report. I have two queries - in one I have stored colleagues forecasts for different months of the year (specificaly for only one category - PTO) and in the other I am having their (weekly/daily/monthly) capacity. What I am trying to get is a count of how many days off they are taking each month based on their individual capacity - i.e.:

"If colleague's weekly capacity is 40 hrs and he is taking 16 hrs off in June I want it to show -> "2 days" in column June, and if other one is having 37.5 hrs weekly capacity and took 15 hrs off -> also "2 days" off".

I was trying to create DAX vlookup column in Forecasting query which is taking the capacity from Colleagues query:

"ColleagueDailyCapacity = LOOKUPVALUE(Colleagues[Daily_Capacity], Colleagues[ColleagueID], Forecast[Colleague_ID)"

to be able to create measure based on that in one query (something like "DaysOff = Forecast[June_2019]/Colleagues[Daily_Capacity]"), but it throws me blank rows...

My forecast table looks like that (all month columns are coming from query and rest from the other):

What could be the proper way to do it?

Thanks in advance for all help!

[v-frfei-msft · Accepted solution]

Hi @Anonymous ,

To create relationship between tables and use RELATED function should work. Could you please share your sample data of your both tables to me so that I can have a test, f you don't have any confidential Information. Please upload your files to One Drive and share the link here.

[Anonymous]

Hello @v-frfei-msft ,

Yes, that's it! I totally forgot about existance of "related" function - it is really useful.

Case solved - thank you!