Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Using multiple date columns in a single slicer

I have a situation where I have 10 different date columns in a single table and I have created a visual (line chart) showing number of requests with respect to start date(one of the date column).

 

Now, as I mentioned above I have 10 different types of dates(Start Date, End Date, Close Date, etc) and so I want to see my visual with respect to all the dates (one date column at a time).

 

I was trying to create a dropdown where I could select any of the date columns and then slicer should work for that particular date.

 

Any leads ?

Need Help

5 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thank you for this Amit. But after this, how can I be able to use the different dates in a slicer.

      I use Relative Date slicer to see the number of requests in last six months (for ex) on the basis of start date.

      Now I want to see the same on the basis of end date only and rest all should be filtered out.

      Same goes on with my different dates.

      • amitchandak's avatar
        amitchandak
        Super User
        6 years ago

        Anonymous , If you have join date to same date slicer and using use relation as given in blog.  Once you have relative date filter on date table, it will filter both start date , end date data on the respective measures.

        Now if you want to separate start and end date you can use these dates as a slicer or you can more than one date table, each working with one date.

         

        Hope this will help.

         

         

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    6 years ago

    Hi Anonymous 

    Create two tables which have no relationships with others.

    date = CALENDARAUTO()
    dimension
    start
    close
    end

    Then create a measure and add it to visual level filter

    Filter Measure =
VAR min_date =
    MIN ( 'date'[Date] )
VAR max_date =
    MAX ( 'date'[Date] )
RETURN
    SWITCH (
        SELECTEDVALUE ( 'dimension'[dimension] ),
        "start", IF (
            MAX ( 'Table 2'[startdate] ) >= min_date
                && MAX ( 'Table 2'[startdate] ) <= max_date,
            1
        ),
        "end", IF (
            MAX ( 'Table 2'[enddate] ) >= min_date
                && MAX ( 'Table 2'[enddate] ) <= max_date,
            1
        ),
        "close", IF (
            MAX ( 'Table 2'[closedate] ) >= min_date
                && MAX ( 'Table 2'[closedate] ) <= max_date,
            1
        )
    )

    Download my file

    https://qiuyunus-my.sharepoint.com/:u:/g/personal/admin_qiuyunus_onmicrosoft_com/EYcT7QDSDStAimy96NTXzd4BwtZLxx1an03lQCxPip9hRg?e=euciYH

     

    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thanks Maggie.

       

      I have found a way to do that which is quite similar to your sugestion.

       

      Now, I have 11 relationships out of which one is active.

       

      My requirement is to apply drill through to the detailed data which I have implemented, but it is working only for that active relationship, it should work for all the 11 relationships.

       

      How can I implement drill through for inactive relationships?