Forum Discussion

Gresi's avatar
Gresi
Frequent Visitor
6 years ago
Solved

Get all Records between two dates based on a slicer selction

Hello at all,

 

i'm looking for a possibility to get all records between two dates from a slicer.

 

That's my slicer for selecting min and max-date.

 

 

Her two measures for getting min and max from slicer:

 

minDate_Selected = CALCULATE(MIN(CalendarDays[Day]);ALLSELECTED(CalendarDays))
maxDate_Selected = CALCULATE(MAX(CalendarDays[Day]);ALLSELECTED(CalendarDays))
 
After that i want to filter the table for getting all records between.
But it looks like the values minDate_Selected and maxDate_Selected will not be taken over for calculation.
 
Now i want to show all records in a timeline like this.
 
It would be great if anyone can help me please.
 
Thank you in advance, Gresi
Need Help
  • Gresi's avatar
    Gresi
    6 years ago

    Hi at all,

    thanks for the answers.

    An addendum again.

     

    In the timeline below, the values of both substitutedStart and substitutedEnd are shown.

     

     

     

     

    minDateTime_Selected and maxDateTime_Selected are determined as follows.

     

    In future, however, minDate_Selected and minTime_Selected as well as maxDateSelected and maxTimeSelected should no longer be hard coded but be selectable via a slicer.

     

    My main problem is that I get the values that come from a slicer not in the editor.

    So I can not expect it.

     

    Maybe anyone knows.

     

    Thanks, Gresi

     

15 Replies

  • AlB's avatar
    AlB
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Hi Gresi 

    It loos like you are creating a calculated table. These are static and will not be affected by slicers. You could use the start and end date in MergedTasks directly on the slicers

     

    Please mark the question solved when done and consider giving kudos if posts are helpful.

    Cheers  Datanaut

    • Gresi's avatar
      Gresi
      Frequent Visitor
      6 years ago

      Hi AIB,

       

      thanks for the answer.

      Should i use two slicers ? One for start and one for end ?

      And how should i substitute start an end when they are overlapping like this ?

       

       

      I can not do like:

       

      substitutedStart = IF(MergedTasks[start] < [minDateTime_Selected]; [minDateTime_Selected]; MergedTasks[start])
      substitutedEnd = IF(MergedTasks[end] > [maxDateTime_Selected]; [maxDateTime_Selected]; MergedTasks[end])
       
      because i don't have minDateTime_Selected and maxDateTime_Selected, both are measures.
      I do need this for every record in my table because the timeline shows substitutedStart  to substitutedEnd for each record.
      If any start or end overlapping minDateTime_Selected or maxDateTime_Selected it should get substituted with it.
       
      I hope you now what i mean.
       
      Thanks, Gresi
  • amaleranda's avatar
    amaleranda
    Icon for Post Patron rankPost Patron
    6 years ago

     

    Gresi 

    If you are using direct query then there is an issue with milliseconds in the time stamps. I had this issue when I try to calculate min and max time in a DQ dataset. Its a analysis services issue marcorusso has a white paper about the same issue. 

     

    If that is the case you may want to truncate the ms part of the timestamp and use the tuncated timestamp in the min and max calculation and comparison. 

     

     

  • amaleranda's avatar
    amaleranda
    Icon for Post Patron rankPost Patron
    6 years ago

     

    Gresi 

    also I noticed in your min/max calculation

     

    you might need to clear all the visual level filters coming from the related visuals. Removing visual level filters really based on the visuls that you are using on the report. 

     

     

    minDate_Selected = CALCULATE(MIN(CalendarDays[Day]);ALLSELECTED(CalendarDays), all(mergedTable))
    maxDate_Selected = CALCULATE(MAX(CalendarDays[Day]);ALLSELECTED(CalendarDays), all(mergedTable))