Forum Discussion

jenesis's avatar
jenesis
Frequent Visitor
2 years ago

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

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

Path
SCD2

3 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

    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

  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    Super User
    2 years ago

    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's avatar
      jenesis
      Frequent Visitor
      2 years ago

      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?