Slicer to Cover Different Data Sources

[Elliott]

Hello,

Simple issue for what I assume should be a simple resolution but can't get my head round it. 

I currently have 2 sets of data, from SQL and Excel, in the same report I am creating.

However, they both show different types of data, the only common column being 'Date' which is simply displaying a range of dates.

Is there any way that I can use 1 slicer on my report for 'Date' that will filter my graphs on both data sources?

Regards,

Elliott

[Bjoern]

If the data belongs together, you could create a connection.

1) Import the SQL table into Power BI Desktop

2) Import excel table into Power BI Desktop

3) Model relationship between data

4) Create nice and fancy reports

5) Integrate a slicer based on a column of your choice. 😉

Please be aware that a join/link on "date" is not the nicest thing to do in most cases. :-]

[Elliott]

Thank you for response, however I did attempt to create relationship before.. receiving the error seen below:

"You can't create a relationship between these two columns because one of the columns must have unique values."

[Bjoern]

Could you please paste an example? I thought you have 2 dimensions with calendar data (=unique values). Otherwise I would try to replace the dates with a ongoing numbre and use it as an idea to join the data.

If the data is otherwise structured, you might have a m:n relationship, which is still a bit tricky in PowerBI, as far as I know.

[itchyeyeballs · Accepted solution]

You could try creating a seperate date table (some examples here - http://community.powerbi.com/t5/Desktop/How-to-mark-Date-table/td-p/3202

Then link your two data sources to this new date table (and not each other). Make sure the date format is the same in all three tables.

Should then let you filter all visulaisations from one slicer

[elkhawase]

the link doesn't work

[Elliott]

Thank you for your response,

Indeed, creating a separate table specifically for 'Date' and linking both 'Date' columns from my other tables has resolved my issue.

Thank you 

[itchyeyeballs]

Good to hear it worked, 

I should have added in my response that you should always use the lookup table as the data source for any slicers to make sure things work as expected.

Coming from a database background (rather than data warehouse) It was a bit counterintuitive for me at first to create a table containing nothing but date information rather than calculate on the fly, however having read up a few sources like this it now makes much more sense