Forum Discussion

WGordon's avatar
WGordon
Frequent Visitor
5 years ago
Solved

Creating a reusable table filter

My problem can be split into two I suppose. The original issue, I would like to have a Calendar filter my main contract table, such that if I select a date then only contracts which started before an...
Need Help
Tips and Tricks
  • WGordon's avatar
    WGordon
    5 years ago

    So, having thought about it for 10 more minutes, I decided to Create a Measure which doesn't return a filter itself but can be used in a filter expression. Therefore the logic still gets reused.
    The filter returns True if the Max date in the Calendar is > than the Contract start date and the Min Date of the Calendar is < the Contract End Data.
    So if the Calendar isn't filtered, all contracts come through.
    If the Calendar is filtered to say a month then all Contracts which are active for any time in that Month come through.
    If you have selected a single Date then exactly contracts which are active on that date will come throuh.

    Is_Within_Date_Range =
    VAR Max_Calendar = MAX('Calendar'[Date])
    VAR Min_Calendar = MIN('Calendar'[Date])
    VAR Max_End = Max(Contract[Expiry].[Date])
    VAR Min_Start = Min(Contract[Inception].[Date])

    RETURN
    AND(Max_Calendar >= Min_Inception, Min_Calendar <= Max_Expiry)

    I would still be interested to know if there's a better way of doing this.

    Cheers,
    WG
WGordon's avatar
WGordon
Frequent Visitor
5 years ago

So, having thought about it for 10 more minutes, I decided to Create a Measure which doesn't return a filter itself but can be used in a filter expression. Therefore the logic still gets reused.
The filter returns True if the Max date in the Calendar is > than the Contract start date and the Min Date of the Calendar is < the Contract End Data.
So if the Calendar isn't filtered, all contracts come through.
If the Calendar is filtered to say a month then all Contracts which are active for any time in that Month come through.
If you have selected a single Date then exactly contracts which are active on that date will come throuh.

Is_Within_Date_Range =
VAR Max_Calendar = MAX('Calendar'[Date])
VAR Min_Calendar = MIN('Calendar'[Date])
VAR Max_End = Max(Contract[Expiry].[Date])
VAR Min_Start = Min(Contract[Inception].[Date])

RETURN
AND(Max_Calendar >= Min_Inception, Min_Calendar <= Max_Expiry)

I would still be interested to know if there's a better way of doing this.

Cheers,
WG