Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
ejordan95
New Member

Building visualizations - linking turnover data tables

Hi all, 

 

I'm stuck.  

 

BACKGROUND:

I'm building out a visual, that shows the # of job requisitions that were open every month.  This is NOT the number of jobs whose open data was in any given month.  

 

I have a data set with open date, close date, job level, location, recruiter, and job requisition ID.  

I have a seperate table with columns titled Month and "count of open requsitions".  

 

The formula in excel in each row next to the corresponding month is: =COUNTIFS(Table4[Requisition Requisition Open Date],"<="&[@Month],Table4[Requisition Requisition Close Date],">"&[@Month])+COUNTIFS(Table4[Requisition Requisition Open Date],"<="&[@Month],Table4[Requisition Requisition Close Date],"")

 

This gets me all jobs that have an open date on or before (ex.) 01/31/2021, with a close date after 01/31/2021 AND all jobs that have an open date on or before 01/31/2021 and have a close date that is blank.  

 

THE ISSUE: 

 

In excel, I can obviously make a visual with the month/ count table and get me what I need (vis. below).  However, I cannot add slicers for recruiter, job level, or location.  It filters the original data set but the calculations are not counting the subtotal, so the table with calculations does not update.  

 

What I need is in PowerBI (since we're moving the dashboard over there,) to create the same visual, but somehow link (?) the two tables so that when we use the slicers it affects the calculated table so the chart reflects the filter.  

e.g.  # of jobs that were open on any given month for job level 6.  

 

ejordan95_0-1630443984045.png

 

I am extremely open to suggestions and appreciate any support you can give, articles or guidance from your own minds would work well! 

 

(hope this makes sense!) 

Best, 

 

2 REPLIES 2
v-jayw-msft
Community Support
Community Support

Hi @ejordan95 ,

 

Using SELECTEDVALUE() function you could get the selected value in the slicer and you could add it to the formula.

If you want us to help with the DAX formula, please share some sample data and excepted result.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523 

 

Best Regrads,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
amitchandak
Super User
Super User

@ejordan95 , with two date like open and close same like a similar problem like HR

 

See if this blog can help https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.