Deconnect relationship?

[bmpi]

I am trying to calculate the count of active tasks in selected period, but I don't know how I should treat the relationships between the tables.

The table 'Task' is connected to 'DimDate' by start date, and when I count the active tasks this realtionships seems to mess it up since I only get the task with startdate = MIN(DimDate) selected.

Measure looks like this:

# Tasks =

    VAR mxdt = MAX(dimDate[Date])

    VAR mndt = MIN(dimDate[Date])

    VAR  CALC = CALCULATE(

                    DISTINCTCOUNT(Task[Task ID])

                        , FILTER(ALL(Task[End Date],Task[Start Date])

                        , (Task[End Date] <= mxdt && Task[End Date] >= mndt)

                    || (Task[Start Date] >= mndt && Task[Start Date] <= mxdt && Task[End Date] >= mxdt)

                    || (Task[Start Date] <= mndt && Task[End Date] >= mxdt)

                                )

                        )

    RETURN

    CALC

I want to count all ongoing tasks, so if I select one day (eg 05.10.2022) I will list all ongoing tasks, not only the one starting on 05.10.2022

[amitchandak · Accepted solution]

@bmpi , refer to the attached file after signature