I know this has been done to death, but trawling through the search results I am struggling to find a solution to what i am sure is a very simple problem.....
I am extracting a dataset that has a variable depth heirarchy, but i only want to use the top level (Epic) and bottom level (Story) item, but i can't work out a way of cutting out the stuff in between and effectively creating a simple 2 level hierarchy.
The dataset is being extracted from Jira via a python script into a sqlite db, and then read into powerbi report, so i do have some control over how I store the data in the database, but I have limited control over how the data comes from the source system as its via a basic api call that just dumps each item in turn. As such, in the python, i have only really been able to identify for a given item - does it have a parent linked to it, and the parent id.
Currently (rightly or wrongly) i have a main table that holds most of the item information - called Issues, this has an issue_id unique primary key. I have also created another table that holds the top level items in (called Port), this too has a unique primary key of issue_id. In an ideal world, I would like to have a field on Issues, that holds the issue_id of the top level item and links to the Port table. This would then allow me to build a view of all work items that need to be complete for a given Port.
The python script builds a table called issuelinks, which has two fields - parentId & childId. Coming from a relational db background i thought i might be able to recursively walk down the tree, but that's not apparently how things are done in powerbi.
Open to any suggestions for DAX or M-Code that might enable me to crack this. I am also willing to accept that the way i have the tables/links might not be ideal and welcome any suggestions.
