cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PetervdV
Helper I
Helper I

Converting 2 excisting dashboards + datasets to use a single powerBI dataset

Hi,

 

I have 2 dashboards both using a separate dataset originating from sql.

These dashboards are published on the powerbi.com service (same workspace) including their owen dataset.

These datasets need to updated separatly which takes time and seem to put a strain on the server with gateway to my sql database.

I figured I don't need to refresh the same data twice if I can use the Power BI-dataset feature.

 

However I am running into this problem that I can't seem to replace my old datamodel with one already present on the powerbi service either on powerbi.com or PBI desktop.

 

Anyone who can tell me how to achieve this (and please don't say empty dashboard connect to the powerbi datasource and c/p the visuals)

 

Edit: Seems important to note that I have the data imported into the dashboards and have used the DirectQuery function.

 

Peter

8 REPLIES 8
Greg_Deckler
Super User
Super User

@PetervdV Make a copy of your PBIX file. Then you could create a new connection to the dataset. This will end up creating a DirectQuery to Power BI dataset connection. Might not be able to do it since the table names are the same. Then you could remove the import-mode queries from Power Query Editor. Might work.


@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Learn Power BI 2nd Edition

@Greg_Deckler thx for the fast reply.

If i copy my PBIX file (either on my laptop or in the service) I have the same issue as the copy also includes the data. 

 

I will edit my opening post with the remark I import the data and did not the DirectQuery function in my original dashboards

@PetervdV 

Let me see if I understand correctly then walk through the change.

Right now you have two file, both of which pull data from SQL.

  • Dashboard 1
  • Dashboard 2

What we want to to is have a single data set in the PowerBI service that both of the dashboards point to so you only have to refresh it once.

Make a copy of Dashboard 1 and rename it something like Data_Set.pbix and publish this file to the service.  Now you have your data set in a separate source from your dashboards.  You can even delete all the report pages from this file.  It is there only to hold the data source.

Next you use the link I sent to remove the connection from your two dashboard files and point them both to the Data_Set that you published to the service.  After you have them switched over to using the PowerBI data set you can publish them up to the service.

Then you just need to set up the refresh on your Data_Set.pbix

You end up with this

  • Data_Set
  • Dashboard 1
  • Dashboard 2

As @Greg_Deckler pointed out, you should be working off backup copies of your reports until everything is working so you don't lose the original work.

@jdbuchanan71 Yes you understood correctly,

 

But the link you set me, the file to download the script in the article is deemed unsafe by my anti-virus software. Executing the script written in the article in Powershell doesn't remove the connections in my file. 

And that is probably due to that in import the data and don't use DirectQuery as the article also states:

 

Note: Running it on a file without a live connection will not have an effect on the file.

@PetervdV I was just saying to make a copy of your Power BI file so that you don't screw it up and can't go back!


@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Learn Power BI 2nd Edition

@Greg_Deckler So to be clear it can't be done when I import the data?

 

Because when I open my dashboard in the desktop and click on Power BI Dataset. That I can't make a live connection as the file is already connected to a different datasource. 

 

jdbuchanan71
Super User
Super User

@PetervdV 

Take a look at this article about hot swapping data connections.  As long as all of the tables and columns are the same it has worked for me in the past.

https://powerbi.tips/2020/05/hot-swap-power-bi-report-connections/

 

@jdbuchanan71  Thx for the fast response tried it but doesn't work. I think it is because I import the data and do not use the DirectQuery function.

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors