Hierarchy Filter Selection

[Anonymous]

Hi,

In my dataset, Program belongs to school and School belongs to the Centre. For example, Centre 2 has 4 Schools under it and they offer together 33 programs and School 2, one of the schools of Centre 2, offers 8 programs. 

Applications measure counts the number of rows in the fact table, ProgPerSchool counts distinct programs offered by a school, AppPerSchool counts Applications for a school, AppAverageSchool is AppPerSchool /ProgPerSchool. Similar measures are for Centre level too.

Applications =   CALCULATE ( DISTINCTCOUNT(Transactions[Transaction ID])  )

ProgPerCentre = CALCULATE (   DISTINCTCOUNT('Dim Programs'[Program Name]),
        ALLEXCEPT ( 'Dim Programs','Dim Programs'[Centre Name] )   )

AppPerCentre = CALCULATE([Applications],ALLEXCEPT('Dim Programs','Dim Programs'[Centre Name]))

Desire Result

For example, If I select the center 2 from the slicer, I want AppPerSchool only to show applications of schools for Centre2 and ProgPerSchool to show Programs of schools for Centre 2, not of the entire dataset.

Similarly, if I select School 12 which belongs to Centre2, I want AppPerCentre to show only applications of Centre 2 and ProgPerCentre to show programs for Centre 2, and not of the entire dataset.

Likewise, if I select a program which belongs to School 12 and Centre 2, I want to AppPerCentre and ProgPerCentre to show details of Centre2 and AppPerSchool and ProgPerSchool to show details of School 12.

Thanking you in advance for your time.

Top Level Hierarchy2nd Level HierarchyLow Level Hierachy

[v-juanli-msft]

Hi @Anonymous 

To make measure work as expected, you could measures as below

ProgPerSchool  

AppPerSchool

AppAverageSchool

pro_school = CALCULATE(DISTINCTCOUNT(Sheet1[program]),ALLEXCEPT(Sheet1,Sheet1[centre],Sheet1[school]))

application = DISTINCTCOUNT(Sheet1[transaction])

app_school = CALCULATE([application],ALLEXCEPT(Sheet1,Sheet1[centre],Sheet1[school]))

ave_school = [app_school]/[pro_school]

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-juanli-msft , thanks for your time and guidance.

The measures that you & I created work the same.  What they don't do is that if I select a program, it should pick automatically average of avg_school and avg_centre for comparison, and right now I have to pick it manually. If the end-user does not select a school and center from slicers after selecting a program, the output will be wrong which I want to avoid.

[Ashish_Mathur]

Hi,

On the first sheet, i just see a lot of Tables and that is what is causing a lot of confusion.  Please explain exactly which cell ha s problem, what answer are you expecting in that cell and why.

[Anonymous]

Hi @Ashish_Mathur ,

Thanks for the response.  I have some serious issue with DAX formula and filter context which gives an erroneous result.

The measures will produce the final output and values of the measure should vary depending on the slicer selection. The measures give correct output for centre selection but it breaks when I select a school from slicer or program from the slicer.

Measures that will be used as final output

When I select a school -1 from the slicer, the AppPerCentre and ProgPerCentre give output for the school-1 and not for centre -1.

Similarly, if when I select program-GGG111 which belongs to School-1 and Centre-1, the output for school and centre is completely inaccurate.

[Ashish_Mathur]

Hi,

Given the multiple Tables there, i will take quite some time to understand your data structure and relationships.  I will not be able to help you with this - Sorry.  Someone else will help you.

[mussaenda]

If i am not mistaken, it is because you are using the ALLEXCEPT function.

Try adding School name and program name on your allexcept

[Anonymous]

Hi @mussaenda ,

I tried it but does not resolve the issue. It works for the center but when I click school slicer, it does not work. 

If I click a school from the slicer, I want to see only detail of that school and detail of related center. Similarly, if I click a program from Program slicer, I want to see details of relates school and center besides that program.

School level does not workProgram level does not workCenter level work

The link to file is here