Forum Discussion
USERELATIONSHIP scenario for filtering across multiple tables
Community SupportHi, savage63073
According to your relationship picture, I am wondering why you used the [Company ID] as the foreign key for all the tables instead of the individual column like [Employee ID], which can cause some trouble if you want to use the columns from multiple tables within one visualization.
Therefore, the best method for me is to use a large fact table linked with some dimension tables with the [Employee ID] as the foreign key. This can make the data model easy to build and be used.
If it’s not possible, you can try this method to take column values directly from another unrelated table using DAX:
For example, if you want to get the [Job Title] from ‘Employee’ in the ‘Departures’ table, you can create a calculated column:
Job Title =
CALCULATE (
MAX ( 'Employee'[Job Title] ),
FILTER ( ALL ( Employee ), [Company ID] = EARLIER ( [Company ID] ) )
)
You can also refer to this document:
https://docs.microsoft.com/en-us/power-bi/guidance/star-schema
Thank you very much!
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you, I really appreciate the reply. I can see why CompanyID is ambiguous, so I should clarify; In this case with our model the ID I am describing is actually the Person ID - and thus used as the primary key to connect the Person (which could be an active employee, a terminated employee, a future employee, a contractor, a candidate, etc.) to their records in the other tables like New Hire, Promotion, etc..
Things like Business Unit, Location, Department and Job Grade need to reside uniquely on each of the fact tables, like Employee, Departure, New Hire etc., because of context. For example, a person may have been hired by the company into a certain department and location, then terminated, then re-hired into a different department in a different location, then promoted to yet another location, this time with a new job grade. So that person as a current employee has the newest values, but the given person's hire rows (2 of them in my example), the departure row, and the promotion row each have what the values were at the time of the given transaction.
And they are all material and significant for what our business needs to know. And it's important that we can filter off of those fields - thus the challnge we have and the original question.
I really do appreciate the reply. We are in the process of getting some consultancy for this and a few other challenges we have, but still happy to hear if anyone else has any ideas. Thanks again!
