Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
osuraperera86
Regular Visitor

How do you create a heirachy with this set of data?

I'm working on a portfolio management solution for my team to create a dashboard to visualise the value created. Until now my model had 3 layers for its hierarchy. Recently the business wanted to add the 4th layer (Tasks) to the hierarchy. There are many projects which don't have any tasks, and I'm struggling to think of a way to create a hierarchy including all four layers. The hierarchy data looks like this. Keep in mind that most data (time stamps, statuses....) are built into the project level and not for the task level. 

TaskProjectKAObjective
azeg
bzeg
czeg
dxeg
exeg
fxeg
dyfg
 qfg
 rfg
 sfg
 lih

 

The heirachy should be Objective -> KA -> Project -> Task

 

g,f,y,d  or  g,f,q


Thank you very much. 

3 REPLIES 3
truptis
Community Champion
Community Champion

Hi @osuraperera86 ,

Try this:

Right click on Objective column -> click on create hierarchy 

Then you will see a new hierarchy created i.e.- Objective Hierarchy 

Then right click on KA -> you will see an option called "Add to hierarchy" -> then select Objective Hierarchy

Repeate same thing for project and task.

 

You will then have a hierarchical format

@osuraperera86 -> please hit the thumbs up and mark it as a solution if it helps you. Thanks.

Cookistador
Helper II
Helper II

Select your Objective column and click on Create hierachy (... in the fields pane) 

Then, select KA and Add to Hierachy --> the one you created, then did the same for Proejct and tasks

You should get something similar to my screen

 

Cookistador_0-1661259971567.png

 

Sorry for the miscommunication. I was after a solution which can be more or less done through a power query. The task level information is not in the same table. There are two SharePoint lists (for this example) extracted. I wanted to see whether I could merge them and create the hierarchy of information inside a table. Atm the model uses bi-directional relationships ( which I try to avoid) - see below.

 

osuraperera86_0-1661261262645.png

 


The question is more about how to make power bi add a line when there is more than one task (when merging the files) and how to ignore when there is no task. Ideally, when merged together the table should hold the hierarchy as I mentioned in the original question but retain data in columns. Not all projects have tasks, but all tasks have projects. 

ObjectiveKey Activity Project Task Planned Start dateStart date
ABCDxxxxxx
ABCExxxxxx
ABJ xxxxxx
FGHIxxxxxx



Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel1

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.