Use one date table

[Anonymous]

Hi everyone,

I`ve using power bi since one year, and i cant solved this.

I need to have one table of dates in order tu use that table to filter many other tables.

The thing is that i cant make a relationship between two tables (which have a relationship between) with the table Date.

For example:

I have this tables:
Employees (ID)
Employees Trainings (employeeID)
Date

I want to relation Training Date with Date table and Employees Entry Date with Date table.

So when i show in a same page the trainings and new employees, i want to filter all dat with a same filter of year or month for example.

Thanks in advace.

Regards

[Anonymous]

Hi @Anonymous ,

I'm sorry I'm not clear about your requirement... Could you please provide some sample data in these three tables and your desired result? Whether the relations among them be set same with the one in below screen shot?

Relationship

Desired result??

Best Regards

Rena

[Anonymous]

Hi @Anonymous 

What i see in your example is that you cant slicer both tables. Employee entry date and training date.

That is my problem.

I want in the same page, to use a slicer from "Date table" which allows me to filter both tables.

As you show, one relation is inactive.
If i use the function "Use relationship" it will work only for that count

Thanks

[jthomson]

I'm confused as to what you're actually trying to do here - I can get you want to look at all employees that started in, say, 2019, that's fine. I can get you'd want to look at all training that was done in, say, 2020, that's fine, but I can't really think of any sensible use to use a single slicer here unless you're wanting to say "I want to see all training done in 2019 by employees that started in 2019". Really think that any use for this sort of thing would use two slicers - what exactly do you want to do with just one slicer?

[Anonymous]

HI @jthomson 

I show u an example. As i say i have a page with multiple cards and i want all to be filter by one slicer, so that the client can select the year and see the changes:

As you can see, i want that de slicer of Year (2020, 2019,2018) filter in the same time: Headcounts, Trainings, Hirings, Terminations, etc

I show you an example of a Power BI report:

https://powerbi.microsoft.com/es-es/partner-showcase/agile-analytics-hr-analytics/

See page "Employee Overview" for example.

Let me know if you understand

Thanks

[jthomson]

Well getting rid of the relationship between employees and training should do it, but without knowing everything else that's going on in your report and with your calculations it's impossible to say if that'd break anything or if that's a good idea

[Anonymous]

Exactly!

I have for example a page which has:

Hirings, Terminations, Trainings, Performance Results, etc

So i want to filter 2019 in all that visualizations in one time with one slicer.
In fact, i want my client to be able to use one slicer to filter all that

Thanks

[parry2k]

@Anonymous what is the issue? if you have inactive relationship between employee and date table, you can make it active using USERELATIONSHIP function.

For example, the following measure will count the employees and if you use this measure, you will able to get the count based on date slicer.

Count Employees = 
CALCULATE ( COUNT ( EmployeeTable ), USERELATIONSHIP ( EmployeeTable[Date], DateTable[Date] ) )

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡