USERELATIONSHIP scenario for filtering across multiple tables

[savage63073]

Hi Experts,

I’m super stuck on an issue, hoping someone will be generous to suffer through this description and provide some advice 😊.

The issue is around filtering and leveraging inactive relationships.  I’ve read up a bunch and have a half-decent understanding of USERELATIONSHIP, but all of the examples I am seeing are about how to leverage a date table for this scenario.  My scenario is different, as I’ll explain.

We’re building an HR dashboard, and here is a simplified view of the data model for illustration:

At the core is the person with a company ID, and then other tables like employee details, new hire details, etc.  Those relationships use the Company ID and are built in the data model, no issue there.

Each of those tables tracks unique data and need to be their own table.  Likewise, each of those tables tracks a common set of fields, like location, business unit, department and job grade. Unfortunately we cannot store those on the person table as a singular value, because those values change over time (for example, the same person may have been hired and departed multiple times over the course of time, at different locations, different departments, etc.)  So they have to exist on each table.

Because I already have the relationships between the person and the other tables, I cannot also create an active relationship between the person table and the other tables for those fields.  Likewise, if I create a dimension table for each one, like a “Location” table and a “Business Unit” table, etc., I cannot create active relationships between it and the other tables because it creates ambiguity.  I can however create them as inactive relationships.

This becomes a big problem because the pages of content in the dashboard need to leverage multiple tables, and need filters on the commonly shared fields like Location, Business Unit, etc where there is only an inactive relationship.  This is where I think measures and USERELATIONSHIP come into play, but I’m not grasping how to do it.  Nowhere in this is any sort of measure that I am trying to create where I would put the USERELATIONSHIP function, nor is there any sort of calculation being performed.  I’m just trying to pull data from multiple sources onto the page, and have one set of filters for Location, Business Unit, etc. that work across all of the tables which contain those fields.

So my question is, what do I do?  Where and how do I create these measures that will make these filters magically work?  Thanks so much for any advice you can give!

[v-robertq-msft]

Hi, @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.

[savage63073]

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!