Max number of students that were at study on the same day

[Anonymous]

Hi; i have records of students that went to some lectures on different date periods (for example one student was from 11.6.2016 to 23.9.2016, and some other was on 25.7.2016 till 10.10.2016, and so on...). The problem is that I need to determine maximum number of students that went to lecture on selected period (calendar slicer based range, for example from 1.7.2017-15.7.2015). I have a formula but it is taking over 10GB of memory to deliver results, and computer and service gives me a memory error. Help please, thanks

TEST_COUNT_STUDENTS_2 = MAXX('Calendar';
SUMX('tblPrimary';
IF(
MIN('Calendar'[Date]) <= [PeriodEndCalendarAdjusted] && MAX('Calendar'[Date]) >= [PeriodStartCalendarAdjusted];1;0)))

PeriodEndCalendarAdjusted = MAXX (
FILTER(
'tblPrimary';
'tblPrimary'[Period start] <= MAX ('Calendar'[Date])
&& 'tblPrimary'[Period end] >= MIN ( 'Calendar'[Date] )
);IF(
MAX ( 'Calendar'[Date] ) <= 'tblPrimary'[Period end];
MAX ( 'Calendar'[Date] );
'tblPrimary'[Period end]
))

PeriodStartCalendarAdjusted = MINX (
FILTER(
'tblPrimary';
'tblPrimary'[Period start] <= MAX ('Calendar'[Date])
&& 'tblPrimary'[Period end] >= MIN ( 'Calendar'[Date] )
);IF(
MIN ( 'Calendar'[Date] ) >= 'tblPrimary'[Period start];
MIN ( 'Calendar'[Date] );
'tblPrimary'[Period start]
))

x

[Anonymous · Accepted solution]

Found solution, check here for full explanation. Thanks for any time spent on my post 🙂

[dkay84_PowerBI]

Can you post a sample of your data? Do you have some kind of unique id for each student? You should probably be doing some kind kind of distinct count of id based on the date filter, then use that in another measure that calculates the max. Using the X functions iterates over every record and performs whatever function(s), leading to memory issues if you're not careful.

[Anonymous]

HI, here is sample. Thanks for any effort placed

Sample

[Anonymous · Accepted solution]

Found solution, check here for full explanation. Thanks for any time spent on my post 🙂

[OwenAuger · Accepted solution]

@Anonymous

You can certainly create 'events in progress' type measures such as in the post you linked to or discussed in this paper.

However, you may get better performance by reshaping your data so that you have a row for every date that a Student was in a particular Class, as the measures become simpler and more work is pushed to the storage engine. This method is discussed in this article on SQLBI.

Following the SQLBI approach with your data, I created a sample model here.

Your original table of 20k rows turns into a transformed table of 12m rows, with columns StudentName, ClassNumber and Date.

The measures I created are:

Active Students in selected period = 
DISTINCTCOUNT( tblPrimaryExpanded[StudentName] )

Max Active Students (fast) = 
MAXX ( 
	VALUES ( 'Calendar'[Date] ),
	[Active Students in selected period]
)

In any case, would be interested in how these different methods perform with your data.

Regards,

Owen

[nickchobotar]

@OwenAuger

Hi Owen,

In the SQLBI method the Italians expand the table with DAX and even though the code is optimized to the max it still takes a little bit of time for the table to generate, so I really like you taking this table expansion logic to the PQ. 

List.Transform(
    {
        Number.From([Period start])..Number.From([Period end])
    }, 
        Date.From
    )  

Your M is nice and easy and most importantly eliminates the necessity to combine the sets through CROSSJOIN() or GENERATE()

GENERATE (
    FactTable,
    FILTER (
        ALLNOBLANKROW ( 'Date' ),
        'Date'[DateKey] >= FactTable[OrderDateKey]
            && 'Date'[DateKey] <= FactTable[ShipDateKey]
    )
)

N -

[Anonymous]

Thanks @OwenAuger, your pointers are excellent. I'm traveling right now, so I'll check your sample model a bit later., but I will for sure try to test both cases. Thank you very much 🙂