Help! Nested Table Solution Creating Over a Billion Tables!

Hi StanleyBlack ,

I've not gone through a full solution, but there's a few things you can quickly do to reduce the size of your final output:

1) Specify reasonable bounds on the data

    1.a) You have people who enrolled 20 years ago or more - do you need to report on all of these, or just those enrolled in the last 5-10 years, for example?

    1.b) Your end date for open enrolments is in the year 2286 - you should create an [endDateCapped] field with the following code to limit the future dates that need rows creating:

let
  Date.Today = Date.From(DateTime.LocalNow())
in
if [END_DATE] > Date.Today then Date.Today else [END_DATE]

2) Don't affirm the negative in your ouput - in your example output you're trying to create a row for every student/date/criteria to say they're 'not' that thing. Your output should be limited to only students/dates/criteria that ARE that thing at any given point. The reporting output can be managed via DAX measures to identify when they're not that thing, you don't need to explicitly hold all that data.

3) This may completely negate 1) and 2) - you don't have to 'explode' your data into a single row for each student/date/criteria combination. You can just load the data in it's original form to the data model and use measures to check the dates to report on. For example, a measure similar to this could tell you how many students had medical conditions over time:

_noofWithMedCondOverTime = 
VAR __cDate =
MAX(calendar[date])
VAR __noofMedCon =
CALCULATE(
  DISTINCTCOUNT(contextTable[PERSON_UNIQUE_ID]),
  contextTable[TAG_IDENTIFIER] = "DEMOGRAPHIC__STUDENT__HAS_MEDICAL_CONDITION",
  KEEPFILTERS(__cDate >= contextTable[START_DATE]),
  KEEPFILTERS(__cDate <= contextTable[END_DATE_CAPPED])
)
RETURN
IF (ISBLANK(__noofMedCon), BLANK(), __noofMedCon)

When applied to a visual that uses the [date] field of a DISCONNECTED/UNRELATED calendar table, DAX will do the date checks for you and count up the values for those that meet the criteria on that date.

Pete