Forum Discussion
DAX - count between 2 dates
Hi phil91 ,
In terms of the active relationship, I guess this would be personal preference to some degree. If your visuals most-frequently utilise metrics based on [start date], then make this one active and vice-versa. If there's no difference, then I tend to make them all inactive to avoid confusion later on.
Regarding number employed during the period, you'll need a value-over-time measure, something like this:
_noofEmployed = VAR date_to_examine = MAX(calendar[date]) VAR noofEmployed = CALCULATE( CALCULATE( DISTINCTCOUNT( yourTable[employeeCode]), KEEPFILTERS( date_to_examine >= yourTable[start date]), KEEPFILTERS( date_to_examine <= yourTable[leave date]) ), CROSSFILTER(calendar[date], yourTable[relatedDateFieldIfUsed], None) ) RETURN IF (ISBLANK(noofEmployed ), BLANK(), noofEmployed )You'll notice that I've removed the crossfilter in this example as this works only when unrelated. If you make both of your relationships inactive, then you can remove the first CALCULATE and the CROSSFILTER line.
Pete
I agree in form it is more of a slowly changing dimension but in function it is a fact table. In my case and it sounds like the case of the original poster, the function of this table is to perform aggregations, primarily record counts. It even has related dimensions that you could consider snowflaked if you wanted to call it a SCD because of it's form or would be regular star schema dimensions if you wanted to call it a fact table. It is an interesting case because it sits between being a fact table and being a dimension. I think this is why the performance is rough and it relies on a "virtual" relationship with the > and < filtering around a selected date. I will read up on aggregations of slowly changing dimensions. I'm just thinking that before PBI, in standard datawarehousing, counting the number of records that classify as a certain status at a given date must've been a requirement. Maybe it is a blind spot in traditional star schemas. I've been so impressed by star schemas though that generally I've found they have names and solutions for almost all analytical data modelling. I just can't find any documentation on this case.
I don't think it's a blind spot at all, you just need to select the correct solution for your environment and required output.
When you talk about "virtual relationships" what exactly do you mean? If you can give some detail around your current setup I'm happy to look at optimising for you - there's almost always something that can be done, especially if you're getting 30s visual load times, that's not normal at all.
Pete
Thank you for your interest in continuing to help me on this. Here is an example of our fact table.
As you can see, it relates to multiple dimensions; however, it cannot have an established relationship with the date dimension as the records in this table are represented by date ranges. The measure pattern you supplied before works but even with this table as the fact in a pure star schema, we have performance issues. The primary measure we are experiencing this slow-ness with computes the count of customers in status 3 on the given date, the count of customers in status 3 3 years before the given date (period start), and divides the two. [Given date status 3 count]/[3 years prior status 3 count].
Ok, so assuming that is the only fact table, I'd probably write your required measure like this:
_status3_pctChange = VAR __currDate = MAX(calendar[date]) VAR __prevDate = DATE( YEAR(__currDate) -3, MONTH(__currDate), DAY(__currDate) ) VAR __currCount = CALCULATE( DISTINCTCOUNT(yourTable[Customer_Key]), FILTER( yourTable, yourTable[Status_Key] = 3 && __currDate >= yourTable[Start Date] && ( __currDate < yourTable[End Date] || ISBLANK(yourTable[End Date]) ) ) ) VAR __prevCount = CALCULATE( DISTINCTCOUNT(yourTable[Customer_Key]), FILTER( yourTable, yourTable[Status_Key] = 3 && __prevDate >= yourTable[Start Date] && ( __prevDate < yourTable[End Date] || ISBLANK(yourTable[End Date]) ) ) ) RETURN DIVIDE(__currCount, prevCount, 0)Obviously you'll want to avoid select 29th February as your target date, or you'll need to work around this.
Unless your table has 50M+ rows, you shouldn't have any significant performance issues with this. If you still do, then there could be something else going on. I'm also making the assumption that your data is imported, not Direct or Live query.
Let me know how it goes.
Pete
Thank you again for your assistance on this. I found a workaround I implemented that I would call "less than ideal" because it uses an additional traditiional transaction level fact table for the active customer count calculation. Here are the measures I was originally using that resulted in the poor performance:
You can see they closely resemble the recommendation you sent, just broken into pieces. If I throw this final measure into a card, it evaluates very quickly. However, we have a requirement to show this calculation as a daily trend over the last 3 years. When I try to put this measure across a date axis in a line chart, it takes over 30 seconds to return a result and I have it filtered for 1 year, not 3.
Edit (some more info):
Peformance results for returning 10 day and 365 day trend:
Relevant portion of model:
Row Counts:
DIM - Customers: 552,477
DIM - Status: 6
FACT - Customer Status: 2,036,733
Thoughts:
I was reading through Kimball's datawarehouse toolkit yesterday for inspiration and found the aggregating snapshot fact table employed in a similar problem concerning inventory levels over time. They logged for each period (monthly, weekly, daily, whatever is required) what the inventory level was for each part at the end of that period. That could be applied to this case but it would result in the 2 million record status fact table to expand for each day the customer has been in our system. This is definitely possible and I can see where the dax performance would improve, I can also see that SQL would probably handle this expansion fairly easily, it just seems like so much data to store in an in memory database just for performance enhancements.