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
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.
