Filter Table Visual by EndDate (Inactive Relationship)

[Zahur]

Hi all,

I have a very simple model and I’m stuck on something that feels like it should be straightforward.

Staff table with columns:

• Person

• StartDate

• EndDate

Date table with:

• Active relationship → Staff[StartDate]

• Inactive relationship → Staff[EndDate]

Goal:

• A Date slicer drives both visuals.

• One table visual shows all people who started in the selected month/year (this works fine via StartDate).

• Another table visual should show all people who left in the selected month/year (based on EndDate).

What I tried:
I created this measure:

Leavers =
CALCULATE (
    COUNTROWS ( Staff ),
    USERELATIONSHIP ( Staff[EndDate], DateTable[Date] )
)

This works correctly in a card visual (I see the leaver count for the selected month).
But when I add Leavers as a filter on a table visual (e.g. show only rows where Leavers > 0), the table goes blank — no rows are returned. It works if I add the Leavers measure as a column in the table but I do not want this.

Question:
How can I filter a table visual so that it shows only those staff whose EndDate falls in the slicer period, given that StartDate is the active relationship?

Thanks in advance!

[Rupak_bi · Accepted solution]

Hi @Zahur ,

hope this meets your expectation

Attaching PBIX as well. If works, accept as solution.

[Shahid12523]

Your Leavers measure works for totals but not row-level filters.

Fix: create a boolean flag measure using the inactive EndDate relationship, then filter the table visual on it:

ShowLeaver =
CALCULATE (
COUNTROWS ( Staff ),
USERELATIONSHIP ( Staff[EndDate], DateTable[Date] )
) > 0

Now in your table visual filter pane → set ShowLeaver = TRUE.

This way only staff with EndDate in the slicer period will appear.

[Zahur]

Thank you @Shahid12523 and @FBergamaschi for your replies. I tried @Shahid12523 measure but it did not work so I modified it to 

mShowLeaver =

IF(CALCULATE (

COUNTROWS ( Staff ),

USERELATIONSHIP (Staff[EndDate], DateTable[Date])

) > 0,1,0)

But that did not work either.

Here is my latest pbix Example.pbix

[Rupak_bi · Accepted solution]

Hi @Zahur ,

hope this meets your expectation

Attaching PBIX as well. If works, accept as solution.

[Zahur · Accepted solution]

Many thanks @Rupak_bi I will accept this as a solution. It creates a measure for use in the table making use of the inactive relationship. It is a shame there was no way to create a measure that worked as a filter but your solution is good!

[Shahid12523]

connect me on linkedin-https://www.linkedin.com/in/shahed-shaikh-4608a110b/

explain me on detaily so i can solve your problem

[Zahur]

Thank you @Shahid12523 but I’d prefer to keep the discussion here so others can also benefit. The problem stated is really simple and I have provided a basic example pbix.

After spending many hours on this problem on forums and ChatGPT, I have decided to stick with the fudge fix of adding my original Leavers measure to the visual table as an invisible column.

Of course, I would welcome and appreciate anyone that is able to solve this very simple but frustating problem.

[FBergamaschi]

Hi @Zahur 

am I understadning correctly that you are using a measure as a filter for the visual? If yes, you need to consider that this creates the evaluation of the measure with a set of filters identified by the columns you grouped.

So to help you I need the pbix, can you share it via private message or put the file in a cloud service and share the link here?

I need to understand how the visual is arranged, your DAX is OK but measures used as filters are complex to manage

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

[Zahur]

Thank you for the quick response Francesco. Yes, I am trying to use the Leaver measure as a filter on the table visual. Here is a link to the pbix Example.pbix.