Need to create the levels and should be expendable in Table or matrix

[projectonline]

@Anonymous 

@SivaMani 

Hi, I am begnieer on Power BI so need complete command or screen shot. Thanks in advance.

Now what i need is.

1. Tasks should be sorted with the WBS like 1 at Top then 1.1 then 1.2 and so on if there is sub task like 1.1.1 or more 1.1.1.1

It should show as below

Level1(Task Name/Main Task/Project Name)

Level2(Task Name)

Level 3(Task Name)

Level 4(Task Name)

2. I don't have column which shows task level as 1,2,3,4,5, i only have WBS which is 1, 1.1, 1.1.1, 1.1.1.1 form

Here is screen shot how does it looks like as of now.

Current state

[SivaMani]

Thanks for mentioning me😉

Are you happy to extract the levels from your TaskWBS column?

You may need to create 4 columns in this case.

[projectonline]

Yes that's fine, but help me to create that first

[SivaMani]

Alright!

Steps:

1.  Go to Query Editor
2. Duplicate WBS column 
3. Replace dot(.) with '|' symbol
4. Close and apply
5. Create Calculated columns using PATHITEM() DAX function

Eg:  Level1 = PATHITEM(TableName[DuplicateWBS],1)

It will work in your case!

Please try it and let me know.

Thanks,

Siva

I appreciate your kudos. Please do mark this as the solution if it solves your issue.  

[projectonline]

I have done it, New calculated coulmn

Tasklevel1 = PATHITEM('Tasks'[TaskWBS - Copy],1)  is created now all the tasks are having the 1 as entry in new calculated coulmn tastlevel1.

[SivaMani]

Great!

Similarly,

Tasklevel2 = PATHITEM('Tasks'[TaskWBS - Copy],2)

Tasklevel3 = PATHITEM('Tasks'[TaskWBS - Copy],3)

Tasklevel4 = PATHITEM('Tasks'[TaskWBS - Copy],4)

Use the newly created columns in your matrix instead of TaskWBS.

[projectonline]

Done

Now i can see in taslevel1 the first digit of taskwbs value similarly in tasklevel2 second digit, taslklevel3 3rd digit, tasklevel4 4th digit of taskwbs-Copy

[SivaMani · Accepted solution]

Sorry for the confusion!

The previous one will give the just value of the level. It won't give like 1 -> 1.1 -> 1.1.1

The below one will work as expected

Steps:

1. Create calculated Column to measure the length
   • WBSLength = LEN('Table'[WBS])
2. Create 4 columns for each level
   1. Level1 = IF( 'Table'[Length] >= 1, LEFT('Table'[WBS],1))
   2. Level2 = IF( 'Table'[Length] >= 3, LEFT('Table'[WBS],3))
   3. Level3 = IF( 'Table'[Length] >= 5, LEFT('Table'[WBS],5))
   4. Level4 = IF( 'Table'[Length] >= 7, LEFT('Table'[WBS],7))

[projectonline]

@SivaMani  Yes it is working upto some extent however it's not 100% accurate, couldn't identify the any other issue.

1. Tasks showing at L0 is actually L3 tasks.

l0 = IF(Tasks[WBSlength]>=0, LEFT('Tasks'[TaskWBS],0))

2. Subtotal of 1 level is differnet when we do drilldown and drill up. I am putting 2 screenshot 1 for Level2 and anoter for level 3.

[projectonline]

i m trying it and will update you back.

my logic is let's search for dot(.) in orginal wbs if count of (.) is zero then level0, if count of(.) is 1 then level 1 and so on, Please suggest if that is doable