Forum Discussion

lguima's avatar
lguima
Frequent Visitor
3 years ago
Solved

Slicer with 2 date fields

Hi, 

  I have the following sample:

 

  Creation_date | Finish_Date | Responsible
  09/01/2022     | 09/05/2022 | John Doe
  09/05/2022     | 09/10/2022 | Mary
  09/10/2022     | 09/15/2022 | Peter

 

I need to create a slicer to filter the date based on fields Creation_date and Finish_date. The idea is like a OR clause: if the selected date is between Creation_date OR Finish_Date the data must be filtered. 

 

Based on the data sample, if I choose the Date 09/05/2022 the two first lines should be filtered.

 

I found many samples using the USERELATIONSHIP function, but I could not get how to use it with my situation. USERELATIONSHIP example are always based on specific measures, but here I need to get all data filtered.

 

Please, Any ideas?

Need Help
Tips and Tricks
Tutorial Requests
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    3 years ago

    Hi, lguima ;

    You need create another date table.

    Date = UNION(VALUES('Table'[Creation_date]),VALUES('Table'[Finish_Date]))

    Then create a flag measure.

    flag = IF(MAX('Table'[Creation_date]) in VALUES('Date'[Creation_date])||MAX('Table'[Finish_Date]) in VALUES('Date'[Creation_date]),1)

    Then setting it.

    The final show:


    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.

3 Replies

  • jthomson's avatar
    jthomson
    Icon for Solution Sage rankSolution Sage
    3 years ago

    What you might need to do is create a separate table which has the primary key of this table, and then expands every date between your creation date and finish date fields into a new row (i.e. for your first line, there would be five rows with each of the first five days of this month in them). Relate that to your main data table, and have the slicer look at the new table. This should then allow you to get what you want

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    Hi, lguima ;

    You need create another date table.

    Date = UNION(VALUES('Table'[Creation_date]),VALUES('Table'[Finish_Date]))

    Then create a flag measure.

    flag = IF(MAX('Table'[Creation_date]) in VALUES('Date'[Creation_date])||MAX('Table'[Finish_Date]) in VALUES('Date'[Creation_date]),1)

    Then setting it.

    The final show:


    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.