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
v-angzheng-msft's avatar
v-angzheng-msft
Community Support
5 years ago

Thank you for sharing, I think the above measure is very effective. Could you please mark your post as Answered since it is working now? It will help the others in the community find the solution easily if they face the same problem with you. Thank you.