Forum Discussion

JLip's avatar
JLip
Frequent Visitor
3 years ago

Filtering related tables.

I want to create a measure which will count amount of closed projects, using relationship between Calendar and ClosedProjects but will be able to return projects details from ActiveProjects table for last month certain project was present. After closure projects won't appear in ActiveProjects anymore. Example - currently measure after drill-through returns 2 owners for one period.

ActiveProjects:

IDOwnerPeriod
1A1/31/2022
2A1/31/2022
3A1/31/2022
1A2/28/2022
2A2/28/2022
3A2/28/2022
1A3/31/2022
2A3/31/2022
3A3/31/2022
1B4/30/2022
2C4/30/2022
3D4/30/2022
3D5/31/2022

 

ClosedProjects:

IDClosing Period
15/31/2022
25/31/2022
36/30/2022

 

Current measure:

ClosedProjects =
CALCULATE(
    COUNT(
        ClosedProjects[ID]
    ),
    USERELATIONSHIP(
        'Calendar'[Date],
        ClosedProjects[Closing Period]
    ),
    CROSSFILTER(
        ClosedProjects[ID],
        ActiveProjects[ID],
        Both
    )
)

 

Need Help

2 Replies

    • JLip's avatar
      JLip
      Frequent Visitor
      3 years ago

      Hi,

      I expect that after I drill through a point in time on ClosedProjects measure (ex. June FY2022) I will see the last owner of this project. So in this case project with ID=3 was closed in period JuneFY22, so last time it was present in ActiveProjects was in MayFY22. So I expect to see single Owner of "D" instead 2 Owners "A" & "D" (because "A" was owner at some point in the past, but project was closed when ownership already moved to "D"). Basically I want to use relationship of Calendar[Date]-ClosedProjects[ClosingPeriod] but leverage the fact that ActiveProjects[ID]-ClosedProjects[ID] are also related instead of using 2 fact tables (ClosedProjects and ActiveProjects) - but maybe that is actually the correct approach in this case?

      Basically I think that ClosedProjects despite the fact of being on the one side of relationship should filter back ActiveProjects for this measure by ClosingDate (and I won't find a match between Period and Closing Period). I wonder if any smart combination of =CALCULATETABLE and =SUMMARIZE will do the trick here. Still the more I analyze it the more I think that what I am trying to achieve is quite unreasonable.