Date Slicer between different files

[Pablinho]

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 want to do is to have only one tab, that has all the visuals, and connect all the tables, having a slicer with dates, that will be changing the data that the visuals display.

Thanks for the help! 

[v-cazheng-msft]

Hi @Pablinho ,

Normally, you could pin visuals or report page to the dashboard. If you pin visuals to dashboard, then these visuals can’t do some operations like interactions. But if you pin report page to the dashboard and there are some Slicers contained on this report page, then you could do some interactions like select a date in the date slicer and filter data on this pinned report page. For more details about pinning a live page to a dashboard, you could refer to this official document: How to pin an entire report page to a Power BI dashboard - 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

[Pablinho]

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]

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

[Pablinho]

Thanks!

I have the data as 3 individual tables, which share the same amount of rows and columns, but have 1 value that changes through time. I also added a "date" column so I can differentiate each of the tables.

Appending the tables work because they are all together now and I can use the "date" value to filter through them. However, I feel that is is horrible to be merging 4300 rows each time I have new data. Is it not possible to have the relationship without the need of mergin everything?

Thank you so much!

[v-cazheng-msft]

Hi @Pablinho ,

If you don’t want to merge or append these tables to one and you still hope they can be filtered by value from one Slicer. You may consider this workaround. I create some sample data per your description.

Table 1

Table 2

Create a Table with all the key values of these two tables.

Table 3 = UNION(DISTINCT('Table1'[index]),DISTINCT('Table2'[index]))

Then, create relationships like this.

Take index column from Table 3(Newly created table) to create a Slicer. Then, it will work like this.

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]

Hi!

It was really insightfull. However, my in "index" columns are the same. It looks like this:

The "index:" column is the same in all the tables. The "date" column is different for tables. And the "val" column is different for all tables. I want to display a graph that has "val" for the X axis and "index" for the Y axis. Then, have a slicer with dates. Based on the selected date in the slicer, I want the graph to show the "val" for the selected date. The "index" value are constant.

It works with the append because everything is in the same file, but that seems super inneficient.

Thank you so much!

[v-cazheng-msft · Accepted solution]

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