[Solved] Counting rows with conditions and context

[chris2]

Hi! 

I have a list of events with an activation and inactivation date. I would like to create a measure which counts the number of active events (i.e. events where this condtion is true: ActiveDate < GivenDate && GivenDate < InactivDate || ISBLANK(InactivDate)) for any give date within the current context of the view.

Example Data for eventframes:

Name	ActiveDate	InactiveDate
ABC	2022-01-01	2022-02-01
DEF	2022-03-04	2022-07-01
GHI	2022-04-06

Example Data for dim_date:

Date

2022-01-01

2022-01-02

...

There are 1:n relationships between Date and ActiveDate (active) and between Date and InactiveDate (inactive).

This is my current approach:

Standing_events = 
VAR __currentDate = LASTDATE(dim_Date[Date])
RETURN
    (
        CALCULATE(COUNTROWS(eventframes),
            FILTER(
                eventframes,
                eventframes[ActiveDate] < __currentDate &&  (__currentDate < eventframes[InactiveDate] || ISBLANK(eventframes[InactiveDate]))
            )
    ))

 It works fine if I create a new table with a row for every date but returns an empty result if I use it as a measure.

Edit: 

This worked for me:

Standing_events = 
CALCULATE(
    COUNTX(
        FILTER(eventframes,eventframes[ActiveDate]<=max('dim_date'[Date]) && (ISBLANK(eventframes[InactiveDate]) || eventframes[InactiveDate]>max('dim_date'[Date]))),
        (raise_recover_eventframes[Eventname])),CROSSFILTER(eventframes[ActiveDate],'dim_date'[Date],None))

@amitchandak : Thanks!

[Jihwan_Kim]

Hi,

Please check the below picture and the attached pbix file.

Standing_events =
COUNTROWS (
    FILTER (
        ALL ( eventframes ),
        eventframes[ActiveDate] <= MAX ( dim_Date[Date] )
            && OR (
                eventframes[InactiveDate] >= MIN ( dim_Date[Date] ),
                eventframes[InactiveDate] = BLANK ()
            )
    )
) + 0

[chris2]

This returned a count of all events. Anyway thank you for your effort.

[amitchandak · Accepted solution]

@chris2 , Seem the case is very similar to my HR blog. Refer if blog or attached files can help

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...