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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Anonymous
Not applicable

Syncing Two Date Columns Through Visual

Hello! 

 

I am trying to connect two different date columns from one table to a neutral Date table, but unfortunately, I cannot have two active relationships. This article was incredibly useful in doing the calculations, but when I click on the bar in my visual, my other visuals only drill through to one of the date columns (open date-the one with an actual active relationship with the Date table). I'm hoping someone here can help me find a way to sync up my open and close dates. When you see the screenshots and attachment, you can see when you click on the bar, you will only return the employees hired that month (using the example provided in the article). I would like to see both employees who have joined and left the company in each month, affecting other visuals in the dashboard as well.

 

https://1drv.ms/u/s!AoiqWZf7fpqIbFhyAs-lT25AreM?e=M5h4za

1 ACCEPTED SOLUTION

@Anonymous , I have taken that file. It seems like the file, I shared on the blog with some modification.

 

Anyways. I added hired count and termination count to the table now when you will drill, both measures will force the data to be visible.

 

Please find the attached file after signature.

 

View solution in original post

4 REPLIES 4
amitchandak
Super User
Super User

@Anonymous , not very clear.

You have :

Terminated Employees = CALCULATE(COUNT(Employee[Employee Id ]),USERELATIONSHIP(Employee[End Date],'Date'[Date]),not(ISBLANK(Employee[End Date])))

Hired Employee = CALCULATE(COUNT(Employee[Employee Id ]),USERELATIONSHIP(Employee[Start Date],'Date'[Date]) )

 

if required you can add these two.

Waterfall you are plotting current employee, which taking care of both Hire and Termination

Anonymous
Not applicable

Hi @amitchandak to clarify, yes I used the Hiring and Terminating columns. Subtracted the two calculations to receive what I want on my visual (can get a better understanding in my onedrive link). If you click on the waterfall visual for a specific month, you will only see employees who had been hired that month in my table visual. For example, 2019 in the waterfall shows we had lost one employee overall that month, but when you click on that year, you see the two employees who had joined that month in my table visual (meaning three left that year). How can I see both the employees who joined and left the company in 2019 in my table? I can't connect both to the Date table because I can only have one relationship. You mentioned creating a duplicate Date table earlier? But I can't picture how that would work.

@Anonymous , I have taken that file. It seems like the file, I shared on the blog with some modification.

 

Anyways. I added hired count and termination count to the table now when you will drill, both measures will force the data to be visible.

 

Please find the attached file after signature.

 

Anonymous
Not applicable

Thank you so much, @amitchandak!!! This solution seems to work! 

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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