Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

@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

@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
Not applicable

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

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

@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

@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
Not applicable

@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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.