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?
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:
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
