Daily total of slowly changing dimension 2 table

[jtspilker]

Main question - Is there a way to create a measure that tracks the total number of people assigned to each value at every given point in time between dates of a date slicer, when that info is stored in a slowly changing dimension 2 table where only changes are recorded with its valid date range?

I have a SCD2 table. In my table is an Id, Value, Category, begin and end dates in which the Value assignment is valid. Customers may have many records in this table due to many changes to their history, or just one if they haven't been recategorized at all.

CustomerId	Begin Date	End Date	M Pool Value	M Pool Category
1	1/2/2021	3/1/2017	Former M_Pool	NULL
1	3/2/2017	8/25/2021	M3	MLD
3	3/3/2017	12/27/2018	M1	MG
3	12/28/2018	8/25/2021	Former M_Pool	NULL
5	3/3/2017	12/27/2018	M1	MG
5	12/28/2018	8/25/2021	Former M_Pool	NULL
7	3/3/2017	8/25/2021	M1	MG
8	12/31/2020	3/1/2017	Former M_Pool	NULL
8	3/2/2017	8/25/2021	M3	MLD

I've created running totals before, but this is a bit different since at the beginning of my date slicer I'll need to capture who is where at that point in time, as well as where everyone is at each day as they drop in and out of the different values.

Right now, I'll I've been able to create is a measure and chart that captures how many changes were made to each category on the date of the change, using the below DAX

RT M3 = 
var MinDate = CALCULATE(MIN(DimDate[Date]), ALLSELECTED(DimDate))
var MaxDate = CALCULATE(MAX(DimDate[Date]), ALLSELECTED(DimDate))
return
CALCULATE(
	DISTINCTCOUNT('M Pool History'[CustomerId]),
    FILTER('M Pool History','M Pool History'[Begin Date] <= MaxDate),
    FILTER('M Pool History','M Pool History'[End Date] >= MinDate),
    VALUES('M Pool History'[M Pool Value])
)

Producing results like:

My data model relates the SCD2 table to a Date Dimension table, with the active relationship between Begin Date and Date, and inactive relationship between End Date and Date

Ideas? The only one I have right now is by brute forcing the SCD2 table into having a record for each customer for each date in scope with what category they had at that time, but that would result in 20M+ rows, and would only grow over time.

[lbendlin]

It will allow you to create a cartesian product between dates and people, and then you can create a measure that calculates your desired value for each combination of person and date. Or a measure that calculates the total people assigned to a particular value over time.

[jtspilker]

Gotcha. So if I'm understanding correctly, I'll still be creating a table that has every customer and their assigned value at every date in my range? I was hesitant to do that because of the large number of rows this would create and was hoping there was some DAX available that would forgo that.

[lbendlin]

No, there's no workaround in DAX (that I am aware of).  If you are concerned about the volumes you may need to do the computations further upstream. Power BI has no memory and what DAX calls variables are actually constants that only live inside the (row) context.

[jtspilker]

Thanks for the discussion, lbendlin!

[lbendlin]

Likewise.  Here's to hoping that Microsoft will respond in kind to Qlik's CDC advancements and implement some features for event based reporting. That would obviate the need for these snapshot gymnastics.

[lbendlin]

Possible approaches would be to use a dedicated disconnected calendar table or to use the CROSSFILTER(,,,none) pattern.  You'll have to test the performance though.

[jtspilker]

Thanks. Do you have examples for either of these methods or be willing to give a brief description of how these could be implemented? I'm unfamiliar with either approach of using a disconnected calendar table or the CROSSFILTER function.

[lbendlin]

This article is covering the CROSSFILTER(,,,None) scenario

Bidirectional relationships and ambiguity in DAX - SQLBI

No, really, it does.  It's a long read but totally worth it.

[jtspilker]

Interesting article!

I think I'm following what the CROSSFILTER can do, but not sure how that would help me in this scenario. I want to track the total people assigned a particular value through time, so how does disconnecting the Value history from the date get me closer to achieving that?