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!

     

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

Thank you wardy912 - I have replicated and I think it works.

Question - it seems the DateType selector is not needed as I have created seperate measures and displayed it in a table?

 

 

Also I don't understand how the IF statement works as I've changed the order it to get the correct values. I have:

Opened

IF(
            [SelectedDateType] = "CreatedDate",
            'Cases'[EndDate] IN VALUES(DateAlt[Date]),
            'Cases'[CreatedDate] IN VALUES(DateAlt[Date])
           
        )
 
Ended
IF(
            [SelectedDateType] = "EndDate",
            'Cases'[EndDate] IN VALUES(DateAlt[Date]),
            'Cases'[CreatedDate] IN VALUES(DateAlt[Date])
           
        )
 
As you can see, everything is the sames apart from the [SelectedDateType] text.
I would have expected the order of the DateAlt[Date] to be the reverse in one of the measures?
wardy912's avatar
wardy912
Icon for Super User rankSuper User
1 year ago

Hi _bs_ 

 

 The DateType selector is only required if you want to display either the createddate or the enddate.

You don't need it since you're displaying the measures together (I misread your initial post, still a useful tip for another scenario).

 

For your situation MohamedFowzan1 has provided the best solution.

You can use inactive relationships with the command USERELATIONSHIP.

Use 2 separate measures as suggested

 

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

 

As for your question about the IF statement:

 

In Opened, you're saying:

"If the user selected CreatedDate, then filter on EndDate"
Which is not what you want.

 

In Ended, you're saying:

"If the user selected EndDate, then filter on EndDate"
Which makes sense.