Using multiple date columns in a single slicer

[Anonymous]

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 ?

[v-juanli-msft]

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]

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?

[amitchandak]

@Anonymous , You have to create a date table and join all dates with that. only one active join other will be inactive. and use userelation to activate join.

In this blog , I have shared working example of this

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

[Anonymous]

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]

@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.