Forum Discussion

Bastaba's avatar
Bastaba
Frequent Visitor
6 years ago

Measure with multiple hierarchy levels

Hi,

I have some issues on creating a DAX measure with multiple hierarchy level.
I have 1 table with unique client code and 4 hierarchy level.
Hierarchy 1 contains Hierarchy 2
Hierarchy 2 contains Hierarchy 3
Hierarchy 3 contains Hierarchy 4

Hierarchy 4 contains Client Code

 

I use the relations as descripted in the screenshot.
Each hierarchy level is connected to the main hierarchy table.
Then each one is connecting to the table with the values.
I am using this kind of relation because 'Code Hierarchy' in 'Table' can contain items from Hierarchy 1 or 2 or 3 or 4.

 

 

When i use drill on a simple graph with the 4 level and a simple measure like Average(Value), i don't get all the data. (i understand why)

I have tried some measures with ALL() / ALLSELECTED() / RELATED() / etc ... but it didn't work ...
Measure =

CALCULATE(
AVERAGE(TABLE
 
[Value]);
...
)

Could you help me please ?

Regards,

2 Replies

  • sturlaws's avatar
    sturlaws
    Icon for Resident Rockstar rankResident Rockstar

    Hi Bastaba 

     

    I don't understand the structure of your data. Could you provide some sample data to highlight what is causing you trouble? Or even better, create a sample report?

     

    Cheers,
    Sturla

  • Anonymous's avatar
    Anonymous
    Not applicable
    Mate, If you have a real hierarchy, all levels must be in the same table. Easy as this. Think about how hierarchies in the Date dimension work...

    Best
    D