Forum Discussion

aliawais's avatar
aliawais
Frequent Visitor
4 years ago

Adding Date Slicer using Multiple Date Columns

Hi All,

 

I'm trying to add a Date Range Slicer to my Power BI Report so every week when i'm producing 4 weeks lookahead report i just have to change dates using date range slicer. I have 5 different milestone's columns each having different dates. I have tried to add all these columns in slicer field but instead of giving me a Date Range filter its showing as a list or drop down.

 

 

My data looks like this:

 

I know i can easily get Date Range slicer by unpivoting these milestone columns but don't want to do that as it will not appear  correctly on my desired visual. Below is the required slicer outcome i need in my report.

 

 

I have read somewhere that it can be done by introducing a new date table and link it with all these milestone's columns but not actually sure how to do it. If anyone can help me with this issue that would be great.

 

link to excel data : https://docs.google.com/spreadsheets/d/1Wgk25QKH03f6j8rSMGtRh4GYjvmsm5nS/edit?usp=sharing&ouid=111415261592969175902&rtpof=true&sd=true 

 

link to Power BI file: https://drive.google.com/file/d/1PNOHRAOY_75CyrVfbTdlKeQE7PbQP31v/view?usp=sharing 

 

Thanks,

Ali

 

 

 

 

 

 

Need Help
Show and Tell

10 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    4 years ago

    Hi,

    If you want a single date slicer, unpivoting is a compulsory step.  After unpivoting, ensure that the Value column has the Data type as Date.  Create a Calendar Table with a relationship (Many to One and Single) to the Value column of the Unpivoted dataset.  To your slicer, drag the Date from the Calendar Table.  

    • aliawais's avatar
      aliawais
      Frequent Visitor
      4 years ago

      Hi,

       

      Thanks for your response. You are right after unpivoting my table I'm able to use the value column as desired date range column without even creating a calendar table. But to do so I'm not able to set up my visual as per project requirements.

      You mentioned that linking Calendar Table to the value table is a solution. Is there any way we can link calnedar table to all of my 5 date columns? I have read some where it's possible but i need to write a code for multiple relationships at one time as Power BI only allows one relationship active at a time.

      Do you have nay idea how to do that?

       

      Thanks Alot 🙂

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        4 years ago

        You are welcome.  You can have only 1 active relationship between 2 tables.  More relationships can be built but they will have to be inactive.  The USERELATIONSHIP() function can be used in DAX formulas to activate the inactive relationships.  I can offer more help if you share some data, describe the question and show the expected result. 

  • v-xiaotang's avatar
    v-xiaotang
    Community Support
    4 years ago

    Hi aliawais 

    Have you solved this question with Ashish_Mathur's help? If you have solved the question, you can accept the answer helpful as the solution or share you method and accept it as solution, thanks for your contribution to improve Power BI.

    If you need more help, please let me know.

     

    Best Regards,

    Community Support Team _Tang

    If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

  • aliawais's avatar
    aliawais
    Frequent Visitor
    4 years ago

    Hi Ashish,

    Sorry for late reply, I was on leave and just joined work today.  I'll explain below one by one:

    I have this query data which i want to show on a gantt chart.

    Gantt Chart will look like this:

    Now on the top i want to add a date range slicer so whenever i want to select 1 Month lookahead period it will Filter all 5 milestones coming within that date range of 1 Month only.

    For this i have created a Calendar which i want to use in my date slicer as i cant use any of my milestones column as it will only filter out that particular milestone and will not show all other milestones coming in that date range.

     

    Relationships are like this at the moment and i know only one relationship can be active at a time and i don't understand how to add multiple relationships from Date Calendar Column to each of my milestone and than activate all those relationships.

     

    I have already done unpivoting all my milestones so i can have only one date column that i could use in date slicer and it works as well but than this visual my compnay is intending to use not showing report in desired format.

     

    These are links to files:

    Excel File 

    Power Bi File 

     

    Please let me know if you need any further inforamtion.

     

    Thanks,

    ALI

     

     

     

     

     

     

     

    • aliawais's avatar
      aliawais
      Frequent Visitor
      4 years ago

      MFelix Hi Hpe you are doing well. I have seen several posts of yours regarding the same matter. Can you please help me with this? 

      • MFelix's avatar
        MFelix
        Super User
        4 years ago

        Hi aliawais ,

         

        How are you?

         

        Just to be clear you want to add a date slicer that filters the full Gantt chart for the specific month you select? So not showing any other months? basically trying to filter the gantt chart for the specific month.

         

        Do you need to use this specific Gantt chart?