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
Repeating values in PowerBI Hierarchy
Hello there, I am trying to create a hierarchy in PowerBI. There can be upto 7 levels in the hierarchy. So, I've defined Path and the 7 levels. However, there are items with less than 7 levels (some even 1). The Matrix table shows the same values of the parent till level 7. Is there a way to control this recursion? Attaching screenshots below. Thanks! Path DAX: Path length < 7 Level definition: Matrix Visual:
Employee Manager Relationship - how to use PATH with monthly data
I want to use PATH() & related DAX functions (PATHITEM, PATHITEMREVERSE, etc.) however my data set from HR is expanded each month to include the latest updates (employees are added, lost, switch teams & report to new managers, etc.) How can I use the PATH() function with the below type of data? Thank you!Dax Parent Child Measure
I need help with a measure. I have a multiple parent hierarchy. Thus far PowerPivot only shows me the fully consolidated value. What I would like to see is the consolidated value for a particular level (meaning, all children and the entity itself), and not the value that remains at the top of the hierarchy. To illustrate my problem I have the following hierarchy: C is the child of A (50%) and B (50%). D is the child of C (100%). D has an income of 100€ and C has an income of 50€. So I have a table for my hierarchy (Nodes), one for the income (Fact Data) and one with the unique names (UniqueNames) I loaded them into the datamodel and connected them as followed: Then I added some columns: First I used the ‘Path’ function, ‘pathlength’ and then I used a lookup-function to create the Level Columns. With these levels I created a hierarchy in the data model: Then I added 4 measures: Total_Fact_Income:=SUM(Fact_Data[Income]) Total_Income:=CALCULATE([Total_Fact_Income];Nodes) PctOwnership:=SUM(Nodes[OwnershipPct]) Consolidated:=SUMX(Nodes;[Total_Income]*[PctOwnership]) When I insert a Pivot table and use the hierarchy for my row and the ‘Consolidated’ measure for my value I get: When fully expanded it shows me 25 for C and 50 for D, when collapsed to C or A it shows 75. So this gives me the consolidated values: When you follow the hierarchy path of A, 75€ will remain when the money is at A. So no matter at which level I am, Excel always show the value that remains when the income is fully consolidated (meaning arrived its end point A or B). I would like to have a measure that does not show me the value for the consolidated income all the way up (here A) , but just to the certain entity (here C). Meaning, in my model so far the consolidated income is 75; I would like to see 150 at level C (own income 50 + income from child 100). Only at the level A I would like to see 75 (150*0,5). This means that you only see the unconsolidated values when fully expanded, When collapsed to C you see the consolidated value to this level (which is simply 100 + 50) and when fully collapsed to A there you see the consolidated value of 75. Can someone help me with this measure?
