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

DAX: Attendance that excludes nursery and reception ONLY when aggregated

Hi everyone,

 

I have a simple problem but one thats frustrating me. I have school attendance data and I want to show on a table or matrix what the attendance is per year group but when it is aggregated at the bottom in the total it filters out Nursery and Recepetion year group data as this is not counted towards official statistics.

 

It should look something like this:

 

Year Group

Attendance

Nursery

90%

Reception

95%

Year 01

100%

Year 02

100%

Year 03

100%

Year 04

100%

Year 05

100%

Year 06

100%

Total

100%

 

I thought I had it but this is as close as I came:

 

Attendance Test1 = 
VAR __NumberOfYearGroups = DISTINCTCOUNT(Attendance[Year])
VAR __AttendancePerYearGroup = 
                            Divide(
                                SUM(Attendance[Attendance]),
                                SUM(Attendance[Sessions]))
VAR __AttendanceAggregated =
                            CALCULATE(
                                Divide(
                                    SUM(Attendance[Attendance]),
                                    SUM(Attendance[Sessions])),
                                NOT(Attendance[Year] IN {"Nursery", "Reception"}))
RETURN
IF(__NumberOfYearGroups = 1, __AttendancePerYearGroup,__AttendanceAggregated)

The problem is that it aggregates to the correct attendance but it adds all possible year groups and provides the aggregated attendance against those year groups not in the subset as shown in the picture below. What can I change to the meausre to make it both optimised and only return against Year groups with data?

 

Thanks in advance guys.

 

AttendanceTest Example.png

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: DAX: Attendance that excludes nursery and reception ONLY when aggregated

Hello @StanleyBlack ,

I think we can get there with an ISINSCOPE and KEEPFILTERS.  Give this a try:

Attendance Pct Corrected = 
IF (
    ISINSCOPE ( Attendance[Year] ),
    DIVIDE ( SUM ( Attendance[Attendance] ), SUM ( Attendance[Sessions] ) ),
    CALCULATE (
        DIVIDE ( SUM ( Attendance[Attendance] ), SUM ( Attendance[Sessions] ) ),
        KEEPFILTERS ( NOT ( Attendance[Year] IN { { "Nursery" }, { "Reception" } } ) )
    )
)

AttendancePct.jpg

2 REPLIES 2
Super User
Super User

Re: DAX: Attendance that excludes nursery and reception ONLY when aggregated

Hello @StanleyBlack ,

I think we can get there with an ISINSCOPE and KEEPFILTERS.  Give this a try:

Attendance Pct Corrected = 
IF (
    ISINSCOPE ( Attendance[Year] ),
    DIVIDE ( SUM ( Attendance[Attendance] ), SUM ( Attendance[Sessions] ) ),
    CALCULATE (
        DIVIDE ( SUM ( Attendance[Attendance] ), SUM ( Attendance[Sessions] ) ),
        KEEPFILTERS ( NOT ( Attendance[Year] IN { { "Nursery" }, { "Reception" } } ) )
    )
)

AttendancePct.jpg

Highlighted
StanleyBlack Frequent Visitor
Frequent Visitor

Re: DAX: Attendance that excludes nursery and reception ONLY when aggregated

@jdbuchanan71 

 

Worked like a charm. I hadn't come across ISINSCOPE before but off to research it now.

 

Thanks alot!

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

Top Ideas
Users Online
Currently online: 54 members 1,063 guests
Please welcome our newest community members: