Hierarchies in Power Pivot

[cr731]

I have a data table that is basically a hierarchy based on columns like this,

[table="width: 500, class: grid"]
[tr]
[td]Level 1[/td]
[td]Level 2[/td]
[td]Level 3[/td]
[td]Level 4[/td]
[td]Level 5[/td]
[/tr]
[tr]
[td]Level 1[/td]
[td]Level 1[/td]
[td]Level 1[/td]
[td]Level 1[/td]
[td]Level 1[/td]
[/tr]
[tr]
[td]Level 1[/td]
[td]Level 2[/td]
[td]Level 2[/td]
[td]Level 2[/td]
[td]Level 2[/td]
[/tr]
[tr]
[td]Level 1[/td]
[td]Level 2[/td]
[td]Level 3[/td]
[td]Level 3[/td]
[td]Level 3[/td]
[/tr]
[tr]
[td]Level 1[/td]
[td]Level 2[/td]
[td]Level 3[/td]
[td]Level 4[/td]
[td]Level 4[/td]
[/tr]
[/table]

And so forth... basically such that each row entry lists one of the items, and ... then for each column identifying the various levels, if the column level is beyond the level of that row entry, it just repeats. I know that's explained poorly but I'm hoping the table illustrates the point.

I cannot change how this data comes in... but I want to create a pivot table of hierarchies with this. The problem is all the repeat entries, for instance, the entirety of row 1 is essentially a duplicate, and therefore in a pivot, it just drills down into identical levels, so it should be excluded. Basically, only rows displaying the lowest level of detail should be kept. One complexity that I didn't show is that level 4 might not always be the lowest level of detail; it can vary by line.

Is there any good way to do this?

 

[ImkeF]

I think it would be a good idea to split your table into 2 before loading into Power Pivot:

1) data table with a unique key
2) lookup/Dimension table with the same key that reflects the hierarchy

You can use Power Query to perform this task. This article might give you an idea of how to do it.

 

[Matt Allington]

This topic is covered this topic is covered at the DAX Patterns website.
Parent-Child Hierarchies – Dax Patterns