Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have 2 tables called DimDate and Enrollment tables.
DimDate[Date] is related to Enrollment[StartDate]
I have a DimDate[Date] as a Date Slider.
I want to distinct count StudentID where Enrollment[StartDate] is earlier than the period startdate and Enrollment[EndDate] is after the period startdate OR Enrollment[EndDate] is NULL
Please, How can I write the dax expression
Solved! Go to Solution.
Try:
StudentCount =
var StartDate = Min(DimDate[Date])
return
CALCULATE(
CALCULATE(
DISTINCTCOUNT(Enrollment[StudentID]),
FILTER(Enrollment,Enrollment[StartDate] <= StartDate),
FILTER(Enrollment,COALESCE(Enrollment[EndDate], DATE(9999,12,31)) >= StartDate)
),
CROSSFILTER(Enrollment[StartDate],DimDate[Date],None)
)
This assumes that your active relationship between Enrollment and DimDate uses the StartDate rather than the EndDate. If it's the other way around than sub in EndDate into the first part of the CROSSFILTER function at the end of the measure.
Try:
StudentCount =
var StartDate = Min(DimDate[Date])
return
CALCULATE(
CALCULATE(
DISTINCTCOUNT(Enrollment[StudentID]),
FILTER(Enrollment,Enrollment[StartDate] <= StartDate),
FILTER(Enrollment,COALESCE(Enrollment[EndDate], DATE(9999,12,31)) >= StartDate)
),
CROSSFILTER(Enrollment[StartDate],DimDate[Date],None)
)
This assumes that your active relationship between Enrollment and DimDate uses the StartDate rather than the EndDate. If it's the other way around than sub in EndDate into the first part of the CROSSFILTER function at the end of the measure.
User | Count |
---|---|
98 | |
90 | |
83 | |
69 | |
67 |
User | Count |
---|---|
113 | |
104 | |
101 | |
73 | |
65 |