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

[Anonymous]

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

[Greg_Deckler]

@Anonymous 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.

[Anonymous]

@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

[jdbuchanan71]

@Anonymous 

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.

[Anonymous]

@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.

[Greg_Deckler]

@Anonymous 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!

[Anonymous]

@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]

@Anonymous 

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/

[Anonymous]

@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.