Daily Spreadsheets of same data combining

[mjmeehan]

Everyday, a report is created of different KPI values based on a part#. There are 2 constants in this report; Part# and Employee column A and Column B respectovely. The rest of the columns are values for the dialy report.

Each day, the spreadsheet is pulled and saved in a network folder. I cannot change the actual excel filers or folder, I can just import into PowerBi. I need to build a 5 day report history. I have been able to do this the hard way by sorting the query by date, removing the top, top2, top3, top4 etc files. This has left me with multiple "queries" named Day2, Day3, Day4 etc. I then create "cards" with a given KPI for a rolling 5 day period (its always the latest 5 files in the foler). This has been working as a solution and shows the groups KPI for each category as a total

Now, my boss is asking if I can do a slicer to choose an emplyee and see the numbers for each employee for the history based on 5 categories. 

Problem is, if I put a slicer in, it only changes the values for that day. I woud have to put in 5 slicers, each for a different day. 

How can I create 1 sllicer that controls the data for all?

[v-jayw-msft · Accepted solution]

Hi @mjmeehan ,

You will need a dimension table that contains all slicer values.

One option is to create an active relationship between this dimension table and all fact tables.

Another option is to create a measure that captures the value of the slicer with the selectedvalue() function and compares it to the value in the fact table. Then use this measure as filter.