Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
102 | |
101 | |
78 | |
70 | |
63 |
User | Count |
---|---|
141 | |
106 | |
101 | |
85 | |
72 |