Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I need to create a calculated dax column to show what depth in a child parent relationsship the current row is on within each group.
ItemID | ParentID | GroupID | OUTLINE LEVEL |
10 | NULL | 1 | 1 |
11 | 10 | 1 | 2 |
12 | 11 | 1 | 3 |
13 | 12 | 1 | 4 |
14 | 10 | 1 | 2 |
51 | 53 | 2 | 2 |
52 | 53 | 2 | 2 |
53 | NULL | 2 | 1 |
54 | 52 | 2 | 3 |
55 | 52 | 2 | 3 |
56 | 54 | 2 | 4 |
157 | 158 | 3 | 5 |
158 | 159 | 3 | 4 |
159 | 160 | 3 | 3 |
160 | 162 | 3 | 2 |
161 | 162 | 3 | 2 |
162 | NULL | 3 | 1 |
As shown in the data table I have a ItemID column a Parent Id column and a Group column.
From this I need to create an Outline Level column to show what level in the hirachy the item is. Null will always be the highest hirachy corresponding to 1 within each group.
Hi @Siboska
Please try
OUTLINE LEVEL =
PATHLENGTH ( PATH ( 'Table'[ItemID], 'Table'[ParentID] ) )
I need something more flexible. It can happen that a items parent is deleted or been placed outside a group. Therefore I somehow need to incorporate that is must do the PathLength logic within each group and IFERROR then just blank it.
@Siboska
You might need splitting by group only if you have ID's that are repeated in different groups. As of the sample data provided that won't be needed. If you have blanks in such data where the ID is not supposed to be blank then you need to fix the data as otherwise you'll obtain wrong hierarchy analysis even if blank out errors.
OUTLINE LEVEL 2 in the following aggregates between different groups yet for this sample data it is providing the same result.
Hi I'm getting the following error using
Each value in 'Tasks'[ItemId] must have the same value in 'Tasks'[ParentId]. The value 'xxxx' has multiple values.
This is why I need to Isolate the Hirachy within each group because ItemID can be used multiple times across groups.
Sure.
The Outline level is defined by how depth the hirachy inside each group is.
I have tried to visualize it:
Each house represent a group. So imagin I can have 10 different house (groups) and then the hirachy needs to be build from that top node.
Rooms, items and so on can be used in the same houses so therefor I need to incoporate the group aspect.
Have tried something similar to this:
Outline Level := VAR CurrentItem = ItemId VAR CurrentGroup = Group VAR ParentItem = ParentTaskID VAR LevelCounter = 1 RETURN IF ( ISBLANK ( ParentTask ), LevelCounter, CALCULATE ( LEVEL ( 'TableName'[ItemId ], 'TableName'[ParentTaskID], 'TableName'[Group] ), 'TableName'[ItemId ] = ParentTask, 'TableName'[Group ] = CurrentGroup ) & "." & LevelCounter )
I somehow managed to find the parents parent and so on, comma seperated but my dax skills ends here.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
25 | |
20 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |