Forum Discussion

AxlCox's avatar
AxlCox
Icon for Helper I rankHelper I
4 years ago
Solved

Using a single slider to filter two different date columns in the same table

Some of the data I work with is essentially a list of tickets with Open and Closed dates.    The data I have, in simple terms, is quantites of tickets. I'm being asked to create a filter (slicer) w...
Need Help
Tips and Tricks
Tutorial Requests
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    4 years ago

    Hi, AxlCox ;

    Yes, you need to create a separate date table that is unrelated to the original table. Then create two measures respectively to calculate the number of open and closed. I created a simple example with the same principle.

    count = CALCULATE(COUNTROWS('table'),FILTER('table',[open date]>=MIN('slicer'[Date])&&[close date]<=MAX('slicer'[Date])))

    The final output is shown below:

    If not ok ,can you share more scenario or screenshots about  more details?
    Best Regards,
    Community Support Team_ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

jdbuchanan71's avatar
jdbuchanan71
Icon for Super User rankSuper User
4 years ago

AxlCox 

You would need a date table and you would link it to your ticket table on both the created date and the closed date.

Only one of the relationships can be active so let's assume it is the one to the created date.

When writing the measure to count tickets based on the closed date you would activate the relationship there.  Something along these lines.

Tickets by closed date =
CALCULATE (
    COUNTROWS ( TicketsTable ),
    USERELATIONSHIP ( TicketsTable[Closed Date], Dates[Date] )
)

Your main ticket count would be off of Created Date so that would just be

Ticket Count = COUNTROWS ( TicketTable )

 Then the slicer from the date table would control both measures and show the correct counts.