Forum Discussion

_bs_'s avatar
_bs_
Icon for Advocate I rankAdvocate I
1 year ago
Solved

Filter on start and end dates

I'm looking to create a slicer that filters the visual on Dates. However the visual will show measures relating to created date and end date, so the slicer needs to work for both scenarios but drive...
Need Help
  • wardy912's avatar
    wardy912
    1 year ago

    Hi _bs_ 

     

     You need to duplicate your current date table and keep it disconnected (no relationships).

    Add the date from this disconnected table to a slicer

    Create another table as follows

     

    DateTypeSelector = DATATABLE("DateType", STRING, {{"CreatedDate"}, {"EndDate"}})

     

    Add this to a slicer

     

    Now create a measure that respects the selected date type, edit to fit your tables

     

    SelectedDateType = SELECTEDVALUE(DateTypeSelector[DateType])

TicketsOpened =
CALCULATE(
    COUNTROWS(FactTickets),
    FILTER(
        FactTickets,
        IF(
            SelectedDateType = "CreatedDate",
            FactTickets[CreatedDate] IN VALUES(DisconnectedDate[Date]),
            FactTickets[EndDate] IN VALUES(DisconnectedDate[Date])
        )
    )
)

     You can use this for ticketsclosed as well.

    This will work independantly of other relationships.

     

    I hope this helps, please give a thumbs up and mark as solved if it does, thanks!

     

MohamedFowzan1's avatar
MohamedFowzan1
Icon for Super User rankSuper User
1 year ago

Hi _bs_ 

You can do this by having an inactive relationship and DAX using USERELATIONSHIP

Set the relationship from the Date table to CreatedDate as Active.
Set the relationship from the Date table to EndDate as Inactive.

For the slicer: Use a slicer visual based on your Date table (NOT the fact table date columns), so the filter is independent from the fact table's relationships.

For measures that need EndDate context, use the USERELATIONSHIP function in DAX to temporarily activate the inactive relationship for aggregation:

Tickets Opened = CALCULATE(
    COUNTROWS('Tickets'),
)

Tickets Closed = CALCULATE(
    COUNTROWS('Tickets'),
    USERELATIONSHIP('DimDate'[Date], 'Tickets'[EndDate])
)


Now both would work based on different joins. Please provide more context if this is not what you are looking for.

If Resolved, Mark as Solution to guide others!