topic DISTINCCOUNT with FILTERs based on unrelated calendartable in DAX Commands and Tips

DISTINCCOUNT with FILTERs based on unrelated calendartable

Baerbel — Mon, 30 Jan 2023 13:52:20 GMT

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

Re: DISTINCCOUNT with FILTERs based on unrelated calendartable

johnt75 — Mon, 30 Jan 2023 15:29:13 GMT

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])

Re: DISTINCCOUNT with FILTERs based on unrelated calendartable

Baerbel — Mon, 30 Jan 2023 20:08:53 GMT

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

Re: DISTINCCOUNT with FILTERs based on unrelated calendartable

johnt75 — Tue, 31 Jan 2023 09:13:29 GMT

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.

Re: DISTINCCOUNT with FILTERs based on unrelated calendartable

Baerbel — Tue, 31 Jan 2023 09:41:44 GMT

Thanks - its a helpfull info for me for similiar issues