Add new colum with values iterating over 2 other colum values

[ivanpuente]

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.

[v-shex-msft]

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

[ivanpuente]

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.

[Greg_Deckler · Accepted 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.

[Greg_Deckler]

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...