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 every one,
I want to add a new column in my report and i don't know how to do it.
Here's an example of my data source
Index | Indentation Level | Task ID | Task Name | Parent Task Id | Expected Result |
1 | 1 | id1 | Task1 | ? | |
2 | 2 | id2 | Task1.1 | ? | id1 |
3 | 3 | id3 | Task1.1.1 | ? | id2 |
4 | 4 | id4 | Task1.1.1.1 | ? | id3 |
5 | 5 | id5 | Task1.1.1.1.1 | ? | id4 |
6 | 5 | id6 | Task1.1.1.1.2 | ? | id4 |
7 | 3 | id7 | Task1.1.2 | ? | id2 |
8 | 4 | id8 | Task1.1.2.1 | ? | id7 |
9 | 5 | id9 | Task1.1.2.1.1 | ? | id8 |
10 | 4 | id10 | Task1.1.2.2 | ? | id7 |
11 | 5 | id11 | Task1.1.2.2.1 | ? | id10 |
I want to fill the "Parent Task Id" column with the values showed in the "Expected Result" (this column isn't part of my data source, it's just in my example to show the expected values) values
How can I fill the "Prent Task Id" with the values of the "Task ID" column and depending on the indentation level of each taks?
Tanks for helping.
Solved! Go to Solution.
This seems to return the correct results.
Column = MAXX(FILTER(ALL('Table6'),[Indentation Level]<EARLIER([Indentation Level]) && [Index] < EARLIER([Index])),[Task ID])
See Table6 of attached.
HI @ivanpuente,
You can try to use following calculate column formula to use task name to find out correspond parent task name and task id.
Parent Task = VAR _indexList = SUBSTITUTE ( [Task Name], "Task", "" ) VAR _path = SUBSTITUTE ( _indexList, ".", "|" ) VAR _parent = "Task" & IF ( LEN ( _indexList ) > 1, LEFT ( _indexList, LEN ( _indexList ) - LEN ( PATHITEM ( _path, PATHLENGTH ( _path ) - 1 ) ) - 1 ), _indexList ) RETURN IF ( _parent <> [Task Name], LOOKUPVALUE ( Table2[Task ID], Table2[Task Name], _parent ) )
Regards,
Xiaoxin Sheng
Hi,
this solution is a good option, but in my example I've used some dummy Task Name values. In real case the Task Name can't be used, because it's free text, so the people can write anything in it.
My real problem needs to get the id from the line having the first indentation level before my line.
So for a line where Indentation Level is 3 (Lines 3 and 7) I want to get the Taks Id of the line with the first appearence of previous level (2 in this case), so the Line number 2.
In the case of line 7 I should go up in the line till I find the first appearance of number 2.
The same way for the line 5 and 6 I want to go up 1 or 2 times to get the first appearance of level 4.
I hope my explanation is clear, and thanks for the reply.
This seems to return the correct results.
Column = MAXX(FILTER(ALL('Table6'),[Indentation Level]<EARLIER([Indentation Level]) && [Index] < EARLIER([Index])),[Task ID])
See Table6 of attached.
You need to use EARLIER. So basically, you need to get the MAX of Indentation Level for ALL of your table where you FILTER Indentation Level < EARLIER of your Indentation Level and you also will have to take into account your Task Name somehow. Once you have that then you can essentially "lookup" your Task ID for that Indentation level and return that. See my article on Mean Time Before Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395...
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 |
---|---|
114 | |
98 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |