Counting occurrences between two dates and filtering by categories

[mficco]

Hi! I have been trying to make this measure work properly but i have not suceeded yet.

Basically I have 3 tables in my model:

- DATE

- EVENTS

- DATA

The EVENTS table looks something like this:

Person ID	Class	Start date1	End date1	Start date2	End date2	…..
1	OK	1/3/2020	5/3/2020	6/3/2020	28/3/2020
2	OK	5/4/2020	10/4/2020	11/4/2020	23/4/2020
3	NO	6/4/2020

And my DATA table like this:

Person ID	City	Department	Area
1	aa	zz	rr
2	bb	xx	ss
3	cc	yy	tt

I have wrote this measure to count the occurrences between start date and end date and it works succesfully.

Occ.1 = CALCULATE(DISTINCTCOUNT(EVENTS[PersonID]);FILTER(ALL(EVENTS);(EVENTS[Start Date1]<=MAX(DATE[Date]) && EVENTS[End Date1]>=MAX(DATE[Date])));EVENTS[Class]="OK")

But when I try to filter the visual by City for example, I just get the days in which the event starts and not all the occurrances between start and end date. 

Any idea or help is welcome.

Thanks!

[Greg_Deckler · Accepted solution]

@mficco - It sounds like you want Open Tickets but hard to say. https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364#M147