Search code examples
powerpivotpowerbidaxssas-tabular

filtering where start & end Dates between selected Date

I have a fact Table that contains people & Thier qualifications. The fact table has two dates, the StartStudyDate & EndStudyDate. This represents the period the students were studying. I then have a Person Dimension, Qualification Dimension, a Grouping Dimension & one Date Dimension.

Im trying to find a count of students who were actively studying on a particular date.

In SQL its relatively simple:

  select a.PaygroupDescription, a.[Qualification], count(a.[PersonID])
 from  (
  select   distinct p.[PersonID], PaygroupDescription, q.[Qualification]
  from [hr].[Appointment Detail] ad
  join hr.Paygroup pg on ad.PaygroupID = pg.PaygroupID
  join hr.Qualification q on q.QualificationID = ad.QualificationID
 join hr.Person p on p.PersonID = ad.PersonID
 join dimdate sd on sd.DateID = ad.StartDateID
 join dimDate ed on ed.DateID = ad.EndDateID
  where sd.date <= 20150101 and ed.date >= 20150101
  ) as a
  group by a.PaygroupDescription, a.[Qualification]

The problem is i cant figure out how to do this in dax. I started out by adding two columns to the fact table in the TabularModel:

ActualStartDate

    =LOOKUPVALUE(
'Date'[Date], 
'Date'[DateID],
'Appointment Detail'[StartDateID])

ActualEndDate

=LOOKUPVALUE(
'Date'[Date], 
'Date'[DateID],
'Appointment Detail'[EndDateID])

I then wrote the measure that checks if one date is selected from DimDate, it gets all distinct rows where the selectedDate is <= ActualStartDate && >= ActualEndDate.

Problem is that this behaves like an absolute dog. if i try to add any attributes for breaking the data down, i run out of memory (at least in 32bit excel). I know i could try 64 bit excel, but my dataset is small, so memory should not be an issue. This is before i even add filters to the calculation for specific qualifications:

EmployeeCount:=if(HASONEVALUE('Date'[Date]),
CALCULATE
(distinctcount('Appointment Detail'[PersonID]), 
DATESBETWEEN('Date'[Date], min('Appointment Detail'[ActualStartDate]),Max( 'Appointment Detail'[ActualEndDate]))
)
,BLANK())

Id appreciate help in understanding the problem correctly as im obviously missing something here regarding the problem & my dax experience is also very light.

enter image description here

Solution

  • I would remove the relationships to DimDate and then use something like the following pattern for your measure:

    EmployeeCount := CALCULATE( COUNTROWS ( 'Person' ),
 FILTER('Appointment Detail',
 'Appointment Detail'[ActualStartDate] <= MAX(DimDate[Date])
 && 'Appointment Detail'[ActualEndDate] >= MIN(DimDate[Date])
))