Forum Discussion

Pablinho's avatar
Pablinho
Icon for Helper I rankHelper I
4 years ago
Solved

Date Slicer between different files

Hi!   I have a dashboard that uses a file each week to update. Right now, I create a new tab each week which uses the same visuals but the data uses a different file with updated values. What I wan...
Need Help
Tutorial Requests
  • v-cazheng-msft's avatar
    v-cazheng-msft
    4 years ago

    Hi Pablinho ,

     

    In addition to use append method to combine tables into one, I think you might try this solution.

    Here are the sample data.

    sheet on 3/24/2022

     

    sheet on 3/31/2022

     

    sheet on 4/7/2022

     

    Then, create a table contains all the dates and take its date column to create the Slicer.

     

    Create relationships for these tables.

     

    Create the following Measure.

     

    DisplayPerDate =
var SelectedDate=SELECTEDVALUE('Date'[Date])
var SumSheet3_24=SUM('sheet on 3/24/2022'[val])
var SumSheet3_31=SUM('sheet on 3/31/2022'[val])
var SumSheet4_7=SUM('sheet on 4/7/2022'[val])
return SWITCH(SelectedDate,DATE(2022,3,24),SumSheet3_24,DATE(2022,3,31),SumSheet3_31,DATE(2022,4,7),SumSheet4_7)

     

     

    After that, I would suggest reverse X axis and Y axis for it’s not easy to implement dynamically X axis in Power BI and they are constant but Y axis will regulate automatically according to the data itself. Then, the result looks like this.

     

     

    Also, attach the pbix file as reference. Hope it helps.

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let me know. Thanks a lot!

     

    Best Regards,

    Community Support Team _ Caiyun

Pablinho's avatar
Pablinho
Icon for Helper I rankHelper I
4 years ago

Hi!

 

Thanks for the interest! However my problem is not the visual interactions, is the data model conections. Currently, I have 3 tables that have the same columns. They share the same ID list and have a column "A" that change from one table to the other but that can only have 5 posible states. Each table is from a different week, so what I want to do is having only 1 graph, that displays the data in column A and a slicer with dates. By selecting the date, I want the graph to display that week information.

 

Thank you!

v-cazheng-msft's avatar
v-cazheng-msft
Icon for Community Support rankCommunity Support
4 years ago

Hi Pablinho ,

 

Do you mean you would like to bring all the data of three datasets to one report page so you can use Date filter to filter these data on this report? If so, I think you could consider using Direct Query for Power BI datasets from Power BI Desktop, then create a report page with them. For more details, you could refer to DirectQuery for Power BI datasets and Azure Analysis Services (preview) | Microsoft Power BI Blog | Microsoft Power BI and Using DirectQuery for datasets and Azure Analysis Services (preview) - Power BI | Microsoft Docs.

 

Or you could merge these data from all your files in the data source itself or in the Power Query Editor(Transform data) and create the report you need from scratch. For more details about merge queries, you could refer to Tutorial: Shape and combine data in Power BI Desktop - Power BI | Microsoft Docs.

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let me know. Thanks a lot!

 

Best Regards,

Community Support Team _ Caiyun