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
This is the solution I have reached as well for a similar model. However, without being able to leverage actual relationships and instead rely on a "virtual" relationship, the calculations are very slow (30s+). I am curious if:
1. Anyone knows what this type of Fact table would be called in traditional datawarehouse-ing
2. Anyone has any tips for enhancing the performance of this measure (without blowing up the grain of the fact table to include every day)
Hi Yanant1020 ,
In terms of the type of fact table this is, it's actually closer to being a dimension table i.e. unique employee id values with additional information about each unique item (e.g. start date, end date etc.) assuming, of course, that if an employee leaves then restarts that they are assigned a new unique employee id.
If the table updates with duplicated id rows when new information is added (e.g. holiday start date, holiday end date) then this would likely be classed as a Slowly-Changing Dimension (SCD) table for the purposes of how you would manage relationships and calculations over it.
In terms of the performance of this measure within your environment, it's almost impossible to say what the issue is and how to improve it without far more information about your data model, the actual data/table it's to be applied over, and what your output requirements are.
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].