Forum Discussion

Zahur's avatar
Zahur
Regular Visitor
1 year 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...
  • Rupak_bi's avatar
    Rupak_bi
    11 months ago

    Hi Zahur ,

    hope this meets your expectation

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

  • 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!

Shahid12523's avatar
Shahid12523
Community Champion
1 year ago

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
    1 year ago

    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's avatar
      Rupak_bi
      Super User
      11 months ago

      Hi Zahur ,

      hope this meets your expectation

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

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

      • Zahur's avatar
        Zahur
        Regular Visitor
        1 year ago

        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.