Data from two pbix files?

[bystarlight]

I have a spreadsheet containing data for Sales 2016. I've built a Power BI report called 'Sales 2016' which includes calculated columns and measures. Aside from the imported data, I've manually entered data into numerous lookup tables.

I have a second spreadsheet with sales from 2015, in the same format and the same headings as the 2016 spreadsheet.

To save myself building a second report from scratch...I copy and pasted the pbix file for Sales 2016 and renamed the copied file to Sales 2015. I opened the Sales 2015 pbix file, clicked on Data Source Settings and changed the source to the 2015 spreadsheet instead of the 2016 spreadsheet. All good! I now have a 2015 sales report which looks exactly like the 2016 sales reports (bar the figures) and it's correctly using the lookup tables, the calculated columns and the measures.

Here's where it gets tricky...

I want to build a third report, this time one that shows trends between 2015 and 2016. At present the data for each year is in two separate pbix files. So I imported the two sales spreadsheets into the one pbix file, along with the lookup tables.

Then I clicked Manage Relationships. I could create Many-To-One relationships between my lookup tables and the 2016 table. But when I tried to create Many-To-One relationships between the same lookup tables and the 2015 table I get error:

"You can't create a direct active relationship between Sales 2015 and CompanyTypes because that would introduce ambiguity between tables Sales 2015 and Sales 2016."

I like my workflow for creating reports for individual years because I'll eventually have to build reports for past and future years. But is there a way of pulling data from two different pbix files for my third trends report? Or am I approaching this the wrong way entirely?

Any advice would be appreciated.

[hugoberry · Accepted solution]

There are no specific rules for what is right and what is wrong for self service BI. I'm sure you have come up with this approach of building the Sales YEAR.pbix files due to some constraints specific to your area.

I would have build the Sales 2016.pbix first, to test my Data Model. Then I would have parametrized the workbook with parameters for the year and save it as a template PBIT.  Please refer to this article for more details https://powerbi.microsoft.com/en-us/blog/deep-dive-into-query-parameters-and-power-bi-templates/

Another approach would be to have a YEAR column in the tables that you construct and union/merge as many sales year data as you want. This way you have you trend analysis done in the same PBIX that contains all of your sales data. In the Report View if you want to have visualisations for only a single year, apply a page level filter with the desired YEAR value.

[bystarlight]

@hugoberry

I think appending the data should be what I need.

Many thanks for your help! 

[v-yulgu-msft]

Hi @bystarlight,

Thanks for sharing your question. Glad to hear that you have found the valid solution to resolve your requirement, please kindly mark the corresponding reply as an answer so that it can help other community members who have similar issues.

Best regards,
Yuliana Gu