PATH-function for user/manager relation in combination with SCD2

[jenesis]

Hello together,

i have some example data like the following:

scdID    userID    userIDsupervisor  csdStartDate    scdEndDate
1        1                           2008-02-02        
2        2        1                  2009-03-04      2015-04-25
3        2        3                  2015-04-25        
4        3        2                  2010-05-03      2014-02-01
5        3        1                  2014-02-01         

scdID is a unique identifier but because of the slowly changing dimension userID is not.
I use a measure and date slider with a date table in background to filter the data to only see the data which is valid at a selected date. The measure is like:

isInDateRange =
    IF (
        MAX ( 'calendar'[Date] ) >= SELECTEDVALUE( 'dim_user'[csdStartDate] )
        && (
            MIN ( 'calendar'[Date] ) <= SELECTEDVALUE( 'dim_user'[scdEndDate] )
            || ISBLANK( SELECTEDVALUE( 'dim_user'[scdEndDate] ) ) 
        )
        , 1, 0
    )

I am filtering the visual with isInDateRange == 1. This works perfectly.

But now I have the task to implement RowLevelSecurity so a manager can see al its childs but under consideration of the slowly changing dimension. So when I select a date I should only see data of people which where below me in hirachy at this cartain date.

I would like to use the PATH function in this context but of course it does not work right away because userID is not unique. So i tried this as a measure:

ManagerPath =
    CALCULATE( PATH( 'dim_user'[userID], 'dim_user'[userIDsupervisor]), FILTER('dim_user', [isInDateRange]==1) )

But this did not work. It results in the same error. Does someone has an idea how to solve this complex setting?

Thank you very much for every hint!
Jenesis

[lbendlin]

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[Sahir_Maharaj]

Hello @jenesis,

Can you please try the following:

1. Enhance Your Data Model: It's important to enhance your data model to include unique identifiers for users. You could create a new column that concatenates the userID and scdID or another unique identifier.

2. Create a User Hierarchy: With the enhanced data model, create a new hierarchy based on the newly generated unique identifier column

3. Calculate Unique Manager Paths: Create a measure to calculate unique manager paths using the newly created unique identifier column.

ManagerPath =
VAR SelectedUser = SELECTEDVALUE('dim_user'[UniqueUserID])
RETURN
CALCULATE(
    PATH('dim_user'[UniqueUserID], 'dim_user'[UniqueUserIDsupervisor]),
    FILTER(
        ALL('dim_user'),
        [isInDateRange] == 1 &&
        'dim_user'[UniqueUserID] = SelectedUser
    )
)

Should you require further details or information, please do not hesitate to reach out to me.

[jenesis]

Hello @Sahir_Maharaj,

thank you for your answer, but there is a problem why this solution will noch work perfectly (I think):

Let's say we have a user "X" and he has the manager "Y" at point in time 1

And this user "Y" has manager "Z" at this point. So the hirachy at point 1 would be: X -> Y -> Z

So now at point in time 2 the manager of user "Y" gets changed to "K". So the hirachy for X from point 2 should be X -> Y -> K.

But this will not trigger any new scd record for the user "X" so looking from the perspective of the old record of user "X" it will result in a wrong (old) PATH (X -> Y -> Z) even after point 2.

Or do you see an error in my thinking in some way?

I tried now another aproach which involves more data preparartion in our data factory. So my data model will now result in one table holding the scd-records for users and one additional table called scd-hirachy, which holds scd2 records for each user, updated whenever anything in the complete hirachy of one user changes.

So this table looks like:

scdIDhirachy   userID    hirachy   csdStartDate   scdEndDate
1              1         1         time1          null
2              2         2-1       time1          time3
3              2         2-3-1     time3          null
4              3         3-2-1     time2      	  time3
5              3         3-1       time3          null

Now i now there is all information correctly available in the data model at each point in time but I am not sure how the RLS query should loook like which checks if a user should the a specific record at a specific point in time which is determined by the date-slider. 
Do you think you can help me to get further with this?