Forum Discussion

_bs_'s avatar
_bs_
Advocate 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 driven of one slicer.

 

DimDate has Active relationship to CreatedDate as well an Inactive relationship to EndDate, so I can create measures that show Number of Tickets Opened and Closed.

e.g,

 

At the moment, the Year Slicer and Year/Month/Day columns use fields based on the Active Relationship, so for Tickets closed, it shows a lower value as it using the Active relationship.

 

I need the Date fields/slicer to be independant of relationships, but some how filter the data on a date or range of dates.

Hope this makes sense. 

Is this possible to do?

Thank you.

 

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!

     

7 Replies

  • MohamedFowzan1's avatar
    MohamedFowzan1
    Super 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!




  • wardy912's avatar
    wardy912
    Super User
    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_
      Advocate 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
        Super 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.

         

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi _bs_,

     

    Thank you for reaching out to the Microsoft Fabric Forum Community, and special thanks to wardy912 , Ashish_Mathur and MohamedFowzan1  for prompt and helpful responses.

     

    Just following up to see if the responses provided by community members were helpful in addressing the issue.

    If one of the responses helped resolve your query, please consider marking it as the Accepted Solution. Feel free to reach out if you need any further clarification or assistance.

     

    Best regards,
    Prasanna Kumar

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    1 year ago

    Hi,

    Share some data, explain the question and show the expected result.  Share data in a format that can be pasted in an MS Excel file,

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi _bs_,

     

    Just following up to see if the responses provided was helpful in resolving your issue. Please feel free to let us know if you need any further assistance.

     

    Best regards,

    Prasanna Kumar