cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
bartekjgs Helper I
Helper I

Colleague capacity and forecast in two different queries

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):

 

forecast.jpg

 

What could be the proper way to do it?

Thanks in advance for all help!

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support
Community Support

Re: Colleague capacity and forecast in two different queries

Hi @bartekjgs ,

 

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.

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

2 REPLIES 2
Community Support
Community Support

Re: Colleague capacity and forecast in two different queries

Hi @bartekjgs ,

 

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.

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

bartekjgs Helper I
Helper I

Re: Colleague capacity and forecast in two different queries

Hello @v-frfei-msft ,

 

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


Case solved - thank you!

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors