separate hierarchy and data files - best approach?

[cturner]

I have two tables that have been supplied to me.  One is the fact table.  Among other columns, it contains the column level, which corresponds to one of 5 levels in the hierarchy table.  It also contains the column entity, which designates which member of that level it is.

So table fact=

pk, level, entity, various measures

The other table is the hierarchy table.  Among other columns, it has separate columns for each level in the hierarchy, of which they are 5.  In that column is populated the value of which branch of the hierarchy tree this lowest level row is a member of.

so table hierarchy =

pk, lowest level, level 4, level 3, level 2, level 1

So, in the first table, I can get a unique identifier by concatenating level and entity.

However, I can't join to the hierarchy table without some logic, which is where it gets fuzzy for me.

I could do a slicer which sets which column to pull from with the same tricks used for a measure selector.  But I'd prefer it to be automatic across all levels

I can do a if (table1[level]="Level 1", table2[level1],if (table1[level]="Level 2... etc but that wont work across unrelated tables.

So what's the best approach to make this work?  I'm looking to be able to navigate the hierarchy and display only the entities at a given level that are children of the parent node on the previous level.

Oh, and lastly, the data for each level doesn't roll up to the level above it.  It's only applicable to the specified level.

Thanks for the assist.

[CheenuSing]

Hi @cturner

Can you post some sample data of the tables in exxcel format on one drive or google drive and share the link to understand the data and formulate a solution.

Cheers

CheenuSing

[CheenuSing]

Hi @cturner

Can you also share the final output you expect.

Cheers

CheenuSIng

[cturner]

Excel with three tabs linked.  this was done manually so I hope I got it right.  Output tab is a loose approximation.

But for any given org level and entity i want to be able to know who the parent and children are.  I can work out the functions for that on my own, I just need the logic to join these two tables to be able to get that output and align the metrics in the data with the hierarchy tree.

https://drive.google.com/file/d/0B-zLl7I_KI5zOURLTnVCTFBBZU0/view?usp=sharing

[CheenuSing]

Hi @cturner,

Pleasse clarify.

1. As per sample output in the excel your hierarchy is org5 to org1.

   Or is the hierarchy org1 to org5

2. Is the value in entity column in dummy data table is  the same as value of individual in dummyhierarchy

Cheers

CheenuSing

[cturner]

Top down Hierarchy is:

Org5

Org4

Org3

Org2

Org1

Individual

level in fact refers to which org level the entity is a member.  So level=org3, entity=3 is an example row.  level=individual, entity=20 is another.  That's the complication - the joining column isn't static.

I messed that up in the output.  Apologies, I've updated it:  https://drive.google.com/open?id=0B-zLl7I_KI5zOURLTnVCTFBBZU0