Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
jtspilker
Helper I
Helper I

Daily total of slowly changing dimension 2 table

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.

CustomerIdBegin DateEnd DateM Pool ValueM Pool Category
11/2/20213/1/2017Former M_PoolNULL
13/2/20178/25/2021M3MLD
33/3/201712/27/2018M1MG
312/28/20188/25/2021Former M_PoolNULL
53/3/201712/27/2018M1MG
512/28/20188/25/2021Former M_PoolNULL
73/3/20178/25/2021M1MG
812/31/20203/1/2017Former M_PoolNULL
83/2/20178/25/2021M3MLD

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:

RT M3 capture.PNG

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

OS Relationships.PNG

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.

9 REPLIES 9
lbendlin
Super User
Super User

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.

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.

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.

Thanks for the discussion, 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
Super User
Super User

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.

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.

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.

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?

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.