Implicit filter penetration of 2 Dates in fact table - Visual Interaction

[iris__bi]

G'day,

Hope someone can help me out on this.
I'm currently dealing with the 2 crucial dates on my main table - project created and closed date.

Using the following DAX, I can plot the open projects of each month assuming the projects are not being cancelled/terminated (going out of the system).

Open Projects =
VAR month_start = SELECTEDVALUE('Calendar'[MonthStart])
VAR month_end = EOMONTH(month_start, 0)
RETURN
CALCULATE(
    COUNTROWS('All Projects Export'),
    FILTER(
        ALLEXCEPT('All Projects Export', 'All Projects Export'[Archived], 'All Projects Export'[1c. Portfolio], 'All Projects Export'[Status],'All Projects Export'[Groups], 'All Projects Export'[1a. Project Type]),
        'All Projects Export'[Created Date] <= month_end &&
        (
            ISBLANK('All Projects Export'[2ze. Project Closure Date (actual)]) || 'All Projects Export'[2ze. Project Closure Date (actual)] >= month_start
        )
    )
)

However, after applying this measure to the line chart, it doesn't allow the click-on lookup to my other table visual to show all the open projects in the specific month. The reason being is that the calendar table in use has active relationship with Created Date, and thereby the filter penetrate through the visual and lock in the month.

Eg: when clicking on the "May" of the line chart, only the created dates fall into May will be shown in the detailed table 

[Anonymous · Accepted solution]

Hi @iris__bi,

Thank you for reaching out in Microsoft Community Forum.

Please follow below for the required output;

1. Create a disconnected calendar table

Calendar_Disconnected =
ADDCOLUMNS(
CALENDAR(DATE(2023, 1, 1), DATE(2026, 12, 31)),
"MonthStart", DATE(YEAR([Date]), MONTH([Date]), 1)
)

2.Modify the Open Projects

measureOpen Projects (Disconnected) =
VAR month_start = SELECTEDVALUE('Calendar_Disconnected'[MonthStart])
VAR month_end = EOMONTH(month_start, 0)
RETURN
CALCULATE(
COUNTROWS('All Projects Export'),
FILTER(
ALL('All Projects Export'),
'All Projects Export'[Created Date] <= month_end &&
(
ISBLANK('All Projects Export'[2ze. Project Closure Date (actual)]) ||
'All Projects Export'[2ze. Project Closure Date (actual)] >= month_start
)
)
)

3.Create a filter flag for the table visual

Is Open In Selected Month =
VAR month_start = SELECTEDVALUE('Calendar_Disconnected'[MonthStart])
VAR month_end = EOMONTH(month_start, 0)
RETURN
IF (
'All Projects Export'[Created Date] <= month_end &&
(
ISBLANK('All Projects Export'[2ze. Project Closure Date (actual)]) ||
'All Projects Export'[2ze. Project Closure Date (actual)] >= month_start
),
1,
0
)

Please continue using Microsoft Community Forum.

If this post helps in resolve your issue, kindly consider marking it as "Accept as Solution" and give it a 'Kudos' to help others find it more easily.

Regards,
Pavan.

[Ashish_Excel]

Hi,

Share the download link of the PBI file.