Forum Discussion

Zahur's avatar
Zahur
Regular Visitor
11 months ago
Solved

Filter Table Visual by EndDate (Inactive Relationship)

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!

  • Zahur's avatar
    Zahur
    11 months ago

    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!

8 Replies

  • 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's avatar
      Zahur
      Regular Visitor

      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

  • Shahid12523's avatar
    Shahid12523
    Community Champion

    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's avatar
      Zahur
      Regular Visitor

      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