Solving the Events-In-Progress Problem in DAX V2.0

This is probably the 5th or 6th post I’ve written on this problem (most deal with MDX, but I did blog about solving it in DAX early last year) but what can I say – it’s an interesting problem! I came across it at work today while working with the 2012 CTP3 version of PowerPivot and found yet another solution to the problem that used some of the new DAX functionality, so I thought I’d crank out one more blog post.

The basic approach is similar to the one I describe here. Using the same Adventure Works data, I can load the DimDate and FactInternetSales tables into PowerPivot V2.0 and I’ll get the following model:


Note that we have three relationships between the two tables: one active one, which is the relationship from OrderDateKey to DateKey, and two inactive ones from DueDateKey and ShipDateKey. If we want to find the number of orders up to the current date using the Order Date we can simply use the following DAX in a measure definition:

, DATESBETWEEN(DimDate[FullDateAlternateKey], Blank(), LASTDATE(DimDate[FullDateAlternateKey])))

Now, if we want to find the number of orders that have shipped up until yesterday we don’t need any special modelling, we can use the new UseRelationship function to force a calculation to follow the relationship going from ShipDateKey to DateKey. Therefore, if we want to find the number of orders that have been placed but not shipped, we just need to take the measure above and subtract the vale returned by the same measure when use this different relationship and change the filter context to be the day before the current day:

IF(ISBLANK(DATEADD(LASTDATE(DimDate[FullDateAlternateKey]), -1, DAY))
, USERELATIONSHIP(FactInternetSales[ShipDateKey], DimDate[DateKey])
, DATEADD(LASTDATE(DimDate[FullDateAlternateKey]), -1, DAY)))

Quite an elegant solution, I think.

5 thoughts on “Solving the Events-In-Progress Problem in DAX V2.0

  1. Chris
    i have an urgent question concerning the 4GB string store limit we have in SSAS .
    currently we reached that limit in a customer dimension table and its not processing update anymore , we will process full and see what happens , but do you have any workaround for that problem ?
    is there a way to hash that string when processing and reverse that hashing value when querying the attribute ?

    1. Chris Webb – My name is Chris Webb, and I work on the Fabric CAT team at Microsoft. I blog about Power BI, Power Query, SQL Server Analysis Services, Azure Analysis Services and Excel.
      Chris Webb says:

      The only workaround is to reduce the size of your strings. The issue is fixed in Denali though.

Leave a ReplyCancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.