DISTINCCOUNT with FILTERs based on unrelated calendartable

[Baerbel]

Dear all,

as DAX beginner I'm currently struggling with my code and I would much appreciate, if somebody could help me to find the right way.

I have two unrelated tables: 

• DIM_calendar  with a date-colum
• FACT_Projects with a start-column and an end-column

All the date related columnes are formatted as date.

In my report I have a Time related slicer for different visuals, where I'm e. g. showing montly results etc.

No I would like to show in another visual only the number of ongoing projects at the end of the selected time period (last month), means all projects which were ongoing at the beginning of the selected period by slicer, but ending earlier than the last month should not be considered. As both tables are unrelated (as I cannot create relation to tart-column and end-column) I'm using the TREATAS-Function.

I tried to create the following measure:

Ongoing_Projects = 
CALCULATE (
    DISTINCTCOUNT( FACT_Project[Project_ID]),
    FILTER
    (FACT_Project[Project_end] >  TREATAS (  VALUES ( DIM_calendar[Date] ),  FACT_Project[end])
        && FACT_Project[start] <  TREATAS (  VALUES ( DIM_calender[Date] ),  FACT_Project[von]))
    )
) 

which don't work 😥 I think the reason is, that I didn't adress the start and the end of the slicer selection correctly, but no idea how to solve this issue.
Can anybody help me ? Thanks a lot in advance for all efforts

Bärbel

[Baerbel]

WoW  😃 you made my day, thanks so much, it works 🤣

In any case you would have another second for me to understand - why it works without a relationship between 

[johnt75]

A relationship with the date table would only help to capture events that happened on a specific date, or within a range of specified dates. Here you are wanting to check whether one date was before the given date range and one was after, so a relationship wouldn't help.

[Baerbel]

Thanks - its a helpfull info for me for similiar issues 

[johnt75 · Accepted solution]

Try

Ongoing projects =
VAR MaxDate =
    MAX ( 'Dim_calendar'[Date] )
VAR Result =
    CALCULATE (
        COUNTROWS ( 'FACT_project' ),
        'FACT_project'[start date] <= MaxDate
            && (
                ISBLANK ( 'FACT_project'[End date] )
                    || 'FACT_project'[End date] > MaxDate
            )
    )
RETURN
    Result

This assumes that there is only 1 entry in the fact table for each project. If there might be multiple entries then you can replace the COUNTROWS with DISTINCTCOUNT( FACT_Project[Project_ID])