Dynamic Group by in DrillDown?

[twiek]

Hi everybody,

I'm facing a problem I can't solve for like half a year now. So hopefully you can give me some advice.

Description of my situation:

- I have data from an API of a project-management-tool (Tasks, Projects, Timelogs, User)

- Tasks and Projects can be linked via ID / parentID relationship.

- There are 8 levels in the hierachy which can be built by joining the Project table with itself 8 times. It then looks like:

FolderID1 | FolderName1 | FolderID2 | FolderName2 | ... | FolderID8 | FolderName8

- Then I can join the Tasks to each level in the Hierarchy which looks like:

FolderID1 | FolderName1 | TaskID1 | TaskName1 | FolderID2 | FolderName2 | TaskID2 | TaskName2 | ... | FolderID8 | FolderName8 | TaskID8 | TaskName8

- I also have tables containing hours and username to each Task.

- The big issue I've got is that the Tasks do not have a unique location (Project) but can be on 'n' locations. E.g. the task "take out garbage" could be in the project "daily routine" and also in "clean the house". The hours spent on this task eventually is now duplicated in my hierarchy table. Anyways, also by just building up the huge hierarchy table a lot of tasks are there duplicated very often.

- My goal ist to build up a visual where I can drill down from Level1 to level8 of the hierarchy showing on the x-achsis how many hours have been spent on the tasks.

- My plan to deal with the duplicates or the n-locations of the tasks is to only use one of the many hour-values of each task in each level of the drilldown. That means I always want to look down the current table based on the current drilldown filters and if there is a task 3/4/n times I will only use 1 value. I hope this is understandable what I am writing ...

I tried a lot of stuff in PowerQuery first, but I get to a point where it just makes no sense anymore.

My current plan was to use kind of a dynamic GroupBy via DAX. Something like

IF DrillDown is on Level1 then GroupBy TaskName1 with the output of the timelogs[hours]

ELSEIF DrillDown is on Level2 then GroupBy TaksName2 with the output of the timelogs[hours]

I tried the IsFiltered() function to simply know which drilldown filters are active and I tried to build a dynamic GroupBy table like

If( IsFiltered(TaskName1), GroupBy(HierarchyTable,TaskName1,"value",sumx(CurrentGroup)=,timelogs[hours]),

    IF(IsFiltered(TaskName2), GroupBY( ...),
        IF(IsFiltered(TaskName3), GroupBy(...),
           ..........

But as I found out it is not possible to use GroupBy dynamically. Is there any way how to look at the current table (with all the drill down filters active) and to figure out how often one task exists or to simply get rid of all tasks but one?

I hope this makes any sense in the way I've written it, it is hard for me to describe it in english.

Thank you in advance,

Max

[twiek]

Thank you for your answer. I didn't know the PATH function but that sounds interesting. I will look into that. However currently the Data comes via SSIS / SQL-Server as described above so I can't really change that for the moment.

My main problem remains that tasks can be on different levels/locations so I somehow need to identify if there are such double-locations in the table with the current drilldown filters and if so to only count the hours spent once.

[lbendlin]

There are 8 levels in the hierachy which can be built by joining the Project table with itself 8 times. 

That sounds rather inefficient.  Have you considered using the PATH functions instead?