cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Bastaba
Frequent Visitor

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.

Sample.png

 

 

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)
Capture.JPG

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 2
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
sturlaws
Super User
Super User

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

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show episode 9

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 25

Ted's Dev Camp - August 25, 2022

Watch Session 25 of Ted's Dev Camp.

Top Solution Authors