I’m trying to get more clarity on using PowerBI workbooks as a dataset for other PBI workbooks.
Basically, I have a PBI workbook that only contains prepped, cleaned, and staged financial data. Almost all of the prepping/cleaning/staging was done in PowerQuery within the workbook. I want to be able to take that data and connect to other PBI workbooks while still maintaining my ability to add data to the model within the workbooks using the financial data.
Currently, when I try to do this by connecting to the workbook as PowerBI dataset it does not allow me to add new data to the model and those buttons on PBI desktop are greyed out.
The purpose of this would be that all of my workbooks use the the same financial data and when that source data is updated in the masterbook, it flows through to the workbooks connecting to the data. I DO NOT want to make changes to the financial data within these connected workbooks, but I do want to be able to use it in dax calculations with other datasets within a workbook’s model.
What would be the best way of accomplishing this? Should this be a dataflow rather than a dataset? Is it possible to upload the dataset from the PBI financial workbook to the project’s workspace without having to recreate it in PowerQuery?
Please let me know if you have any questions or if anything is unclear. I appreciate any and all feedback.
Really interesting question, thank you @Sdowney. @Heather , I remember doing this quite a while back now where we would publish the data set from the “source pbix” and let other subsequent .pbix visualisations connect to the “Source pbix.” If we wanted any new measures added, we would have to add them in the “Source pbix” as well as if we wanted to bring in an additional dataset - again to the “source pbix.” Then I remember hearing that something changed, with regards to combining multiple sources - which your posted article covers. Still I have a few questions for my own clarification:
Question 1: Am I correct that as I have described this indeed was the “standard” way of working with a published datatset? Question 2: In your opinion, if you could get away with it, would you recommend to stick to the “everything in one dataset if you can” approach? (additional measures, additional data - all to be added to the source pbix)?
Yes, prior to the Direct Query update (and possibly one other update that I cannot recall specifics of now) - you had to add everything into the source dataset, there was no way to combine additional sources for just one or two reports.
As for question #2, for myself, I have a handful of datasets, each with their own specific area
Example:
Accounting
Sales
Warehouse/Delivery
Purchasing
And while some measures (such as total quantity), might be present in all four datasets, I’m OK with that and keeping my datasets focused. With the way our team uses PowerBI, there is not often need for overlap (if you’re looking for purchasing data, you don’t generally need to see full customer information- just the account name is often enough)
Because I keep the datasets focused, I rarely find myself adding additional measures to a report that does not also belong in the full dataset.
Hi @Sdowney, we’ve noticed that no response has been received from you since October 21.
We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.