cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
CarlsBerg999
Helper V
Helper V

Using PowerBI Datasets in multiple reports

Hi,

 

I'm trying to use a single PowerBI Dataset for multiple reports. The issue here is that i uploaded the dataset with the original report and now modifying the dataset is very cumbersome if I'm working with other reports. Are Measures supposed to be report specific and the dataset for all? How do you work with a single dataset in power bi?

1 ACCEPTED SOLUTION
v-angzheng-msft
Community Support
Community Support

Hi, @CarlsBerg999 

 

Q:

In other words, we have 3 icons on the left side (Report, Data and Model). I only see Report and Model when i open a report that is connected to the PowerBI Dataset. I need to download the original PowerBI Dataset from the service to add calculated columns or tables. Is this a setting thing?

Yes ,it's a setting thing. When you connect to a Power BI dataset, you can only see the Report and Model Icons, and the connection is Live Connection. You cannot create calculated columns or calculated tables, but you can still create report-level measures.

vangzhengmsft_0-1641881656203.png

You can think of it as creating a copy connected to the source data, so you can't modify the original PowerBI Dataset directly. When you download the original PowerBI Dataset from the service, then you can modify the original PowerBI Dataset.

 

Q:

What becomes difficult is determining where a calculated column or a measure is used. Is there a functionality of "Where used" or some way to get this data? 

Power BI desktop itself doesn't offer such a feature, but you can still use third-party tools to help you.

Refer to my answer in the thread below to help you:
Unused measures affecting performance?

 

more:

Monitor/Measure & Improve Power BI Dataset Performance

 

Hope this helps.

 

 

Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

7 REPLIES 7
v-angzheng-msft
Community Support
Community Support

Hi, @CarlsBerg999 

 

Q:

In other words, we have 3 icons on the left side (Report, Data and Model). I only see Report and Model when i open a report that is connected to the PowerBI Dataset. I need to download the original PowerBI Dataset from the service to add calculated columns or tables. Is this a setting thing?

Yes ,it's a setting thing. When you connect to a Power BI dataset, you can only see the Report and Model Icons, and the connection is Live Connection. You cannot create calculated columns or calculated tables, but you can still create report-level measures.

vangzhengmsft_0-1641881656203.png

You can think of it as creating a copy connected to the source data, so you can't modify the original PowerBI Dataset directly. When you download the original PowerBI Dataset from the service, then you can modify the original PowerBI Dataset.

 

Q:

What becomes difficult is determining where a calculated column or a measure is used. Is there a functionality of "Where used" or some way to get this data? 

Power BI desktop itself doesn't offer such a feature, but you can still use third-party tools to help you.

Refer to my answer in the thread below to help you:
Unused measures affecting performance?

 

more:

Monitor/Measure & Improve Power BI Dataset Performance

 

Hope this helps.

 

 

Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

aj1973
Community Champion
Community Champion

Hi @CarlsBerg999 

Changing anything in a Dataset for one report might hurt the other reports attached to that Dataset. Exemple: Changing a measure that is used in multiple reports it will affect them reports. what you can do is add a new measure, or calculated column or Calculated Table and use it to update your specific report. Be aware that you can't change anything (names,columns,measures..) in the existing dataset or you will hurt the other reports.

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

Hi, Actually it seems i can't even view the underlying data of the original PBIX file. This means that i can't add calculated columns or calculated tables to any reports, except the original one. In other words, we have 3 icons on the left side (Report, Data and Model). I only see Report and Model when i open a report that is connected to the PowerBI Dataset. I need to download the original PowerBI Dataset from the service to add calculated columns or tables. Is this a setting thing? I am the admin so this shouldn't be an issue.

aj1973
Community Champion
Community Champion

Hi @CarlsBerg999 

1st When you only see Report and Model Icons it means that the dataset is in Direct Query Mode or Live connection Mode Connection. DirectQuery, Live Connection or Import Data? Tough Decision! - RADACAD

Therefore you can't add calculated Columns or Tables to your Model. Here is some references that might help if it's DQ. Live connection No.

Problem: Direct query and calculated columns - Microsoft Power BI Community

Solved: Add custom column in Direct Query - Microsoft Power BI Community

 

The other thing you mentioned is Downloading the Dataset from the service, You defenitly need to be granted permission from Power Bi administrator.  

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

bcdobbs
Super User
Super User

I can share some of my journey.

 

When I first started with Power Bi I came across the videos recommending splitting reports and datasets which I did and regretted it! The problem was two fold... my data models weren't very good and my dax was worse! Result of this was lots of little work arounds to make reports work. In the end I gave up with that route for a while.

 

I've since taken the time to really get up to speed with DAX and best practice data modelling with power bi and am now back to a single dataset.

  • Model makes proper use of dimensions and fact tables.
  • Tables, columns and measures are all clearly lablelled (with spaces!)
  • Really think about naming!

In terms of maintaining it we switched to PPU licences which enables the XLMA end point. Effectively the model becomes an Analysis Services instance and I modify it using Tabular Editor 3 (but 2 would be fine). This has the big bonus of working purely on the underlying model.bim json file which plays perfectly with git for version control. I think that more than anything has made it workable.

 

In terms of model vs report measures. The answer is both. If I'm working on a report and need something very bespoke it goes into the report (think about naming here incase you need ever move it into the main model). Any time I need something that I think will be usefull else where I go back and modify the main model.

 

Happy to chat further if you want to share some specifics of things you're fighting with.

 

This is worth a read: Separate reports from models in Power BI Desktop - Power BI | Microsoft Docs

 

I can also highly recommend everything SQLBI have to offer. (Mastering Tabular, Data modelling and Mastering DAX are worth every penny.)



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

This is sort of related: I have a pretty big and complex model that is used by three reports. Each of these three reports have multiple sheets and visuals. What becomes difficult is determining where a calculated column or a measure is used. Is there a functionality of "Where used" or some way to get this data? Often i have to scroll through all visuals before changing / altering a measure. Other "trick" i do is to download a copy of the file and just delete the measure then look for errors.  

I've not tried it yet but I think this might help:

https://www.thebiccountant.com/2021/08/23/power-bi-cleaner-gen2-now-covers-analysis-services-models-... 



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!