topic PATH-function for user/manager relation in combination with SCD2 in DAX Commands and Tips

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

jenesis — Thu, 31 Aug 2023 11:02:20 GMT

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

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

Sahir_Maharaj — Sat, 02 Sep 2023 20:11:39 GMT

Hello jenesis,

Can you please try the following:

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.
Create a User Hierarchy: With the enhanced data model, create a new hierarchy based on the newly generated unique identifier column
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.

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

lbendlin — Sat, 02 Sep 2023 20:12:58 GMT

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-Forum/ba-p/963216
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/1447523

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

jenesis — Tue, 05 Sep 2023 08:19:49 GMT

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?