Multiple level Hierarchy/Drilldown

[Anonymous]

I am currently struggling to format a chart with multiple levels of hierarchical data. An example of this data is 

1. Operations
   1. Air & Climate
      1. Greenhouse Gas Emissions
      2. Outdoor Air Quality
   2. Buildings 
      1. Building Operations 

Where "Operations" = "Air & Climate" + "Buildings" and "Air & Climate" = "Greenhouse Gas Emissions

" + "Outdoor Air Quality"  and so on.... 

Now with saying these categories described above are all values for different schools. I currently have each school per row and each one of the categories (with no difference between eachother regardless of hiearchy) seperated in columns. I am struggling to format my chart in a way that I can kind of drill down looking at total "Operations" then "Air & Climate" and "Buildings" and then to "Greenhouse Gas Emissions" and so on. If anyone has any suggestions on how to format this PLEASE do not hesitate to reach out. 

Thank you!! 

[LivioLanzo · Accepted solution]

@Anonymous

check out the file: https://1drv.ms/u/s!AiiWkkwHZChHjzGtI-Xx9wDMAKfG

[v-frfei-msft · Accepted solution]

Hi @Anonymous,

Please refer to the online documents as below.

Understanding Functions for Parent-Child Hierarchies in DAX

PATH Function (DAX)

Regards,

Frank

[Anonymous]

You will have to modelate your tables in order to get these categories in rows instead of in columns. May be you can create a table with all the categories and make a relation between schools (with the lowest level category in a row ) and your dimension table called categories.

CATEGORY 1     CATEGORY 2     CATEGORY 3                                                SCHOOL ID         CATEGORY 3

         A                    AA                    AAA                                                                 X                         AAA

        A                     AA                    AAB                                                                 Y                          BBA

        A                     AB                    ABA               ------------------------->            Z                         ABA

        B                     BA                    BAA

        B                     BB                    BBA

Regards

[Anonymous]

markross92..... 

I apologize if I am misunderstanding but if I only have my lowest level of data present then I would not be able to drilldown through? For example having a bar chart with just total "Operation" for each school, then drill to "Air&Climate" and "Buildings" for each school and so on. 

[LivioLanzo]

Hi @Anonymous

what does your data table looks like?

also, what does the whole hierarchy look like?

[Anonymous]

LivioLanzo...... 

My data table: 

School ID     Operations    Air&Climate    GreenhouseGas    OutdoorAirQuality    Buildings    BuildingOperations

School A      A                         AA                   AAA                       AAAA                        AB                 ABB 

School B     

School C 

where, 

A= AA+AB

AA = AAA+AAAA

AB=ABB 

There are more sections to the hiearchy but they follow this same type of format. I figure if I can understand how this set of data should be formatted I will be able to apply it to the other categories. 

Hope this helps, thanks! 

[LivioLanzo · Accepted solution]

@Anonymous

check out the file: https://1drv.ms/u/s!AiiWkkwHZChHjzGtI-Xx9wDMAKfG

[Anonymous]

Took a look an thank you so much for providing that, it was extremly helpful.

I was wondering though if you could link me to some documentation or further explain how the path column works in your items table?