A Fine Slice Of SQL Server
Gilbert Quevauvilliers wants to go to infinity and beyond:
By default, when using Power BI Premium or Power BI Premium per user the dataset size is set to 10GB.
I have had the wonderful experience of refreshing my dataset and getting the following error:
In the steps below I will show you how to change this setting to allow for larger dataset sizes.
There are a few steps involved, but hey, if you’re paying for Premium, it’s worth a few steps to get this.
Comments closedChris Webb has an interesting problem to solve:
This week a customer came to me with the following problem: they had scheduled the refresh of their dataset but their source data wasn’t always ready in time, so the old data was being loaded by mistake. The best solution here is to use some kind of external service (for example Power Automate) to poll the data source regularly to see if it’s ready, and then to refresh the dataset via the Power BI REST API when it is. However, it got me thinking about a different way of tackling this: is it possible to write some M code that will do the same thing? It turns out that it is, but it’s quite complicated – so I don’t recommend you use the code below in the real world. Nevertheless I wanted to write up the solution I came up with because it’s interesting and you never know, it might be useful one day.
Read on for the less-than-optimal solution, but do check out the better solution Chris describes.
Comments closedAlberto Ferrari walks us through ranking based on multiple columns:
DAX offers the RANKX function to compute ranking over a table, based on measures or columns. One limitation of RANKX is that it is only capable of ranking using a single expression. Oftentimes it is necessary to use multiple columns to obtain a ranking, either because the business requirement dictates it, or because you want to rank ties with different criteria.
As a demonstration, we rank customers based on their purchase volume. To artificially introduce ties, we use the Rounded Sales measure, that rounds the sales amount to the nearest multiple of one thousand. Using Rounded Sales, several customers show the same amount of 10,000.00. Because they are ties, their ranking must now be defined by alphabetical order based on their names.
Read on for two methods to solve this problem.
Comments closedPhil Seamark has a new tool for us:
Last week I was honoured to take part in the latest edition of the Power BI Dev Camp which is run by my colleague Ted Patterson. It was a fun session which I enjoyed.
As part of the Dev camp, I walked through some of my recent Visual Studio Code based blog posts on how to perform various tasks against models hosted in Power BI desktop.
While preparing for the session, Ted and I agreed that it might be helpful to create a small external tool that could automatically format all DAX expressions in a Power BI model. The idea is to leverage the excellent DAX Formatter API provided by the good folks at SQLBI. This API is the same endpoint used when you format your DAX using DAX Studio.
Read on for more details.
Comments closedPaul Turley continues a series on doing Power BI the right way:
Back in the day, when we created BI solutions, reports and data models were separate. If you created a cube or Tabular model with Analysis Services, it was developed with Visual Studio and deployed to a server. Reports could be authored and deployed to the report server, separately. Now, with Power BI Desktop, you author your data model and report in the same development space and then deploy the whole kit and kaboodle to the service at once.
Secretly, Power BI actually separates the report from the data model (called a dataset in the service) and gives them both the same name. This is very convenient for self-service projects because it is quick and easy to make changes to the data model, queries and measures if you need to make a report enhancement. This is all well and good for small, one developer projects but what about those larger scale solutions where the data model and reports are developed by different folks, or when multiple reports are connected to the same data model?
At what point does it make sense to separate the data model and reports into separate files?
Read on and let Paul illuminate.
Comments closedAdam Saxton points us toward good resources for learning about Power BI:
Just starting with Power BI? Maybe you’ve been using Power BI for a while and have hit a wall. We wanted to call out some items that help with a lot of conversations we have over and over again. Take your skills to the next level!
Check out the video and click through for links to helpful resources.
Comments closedImke Feldmann shows how to refresh Power Query results on protected sheets in Excel:
When working with Power Query in Excel you might want to refresh Power Queries on protected sheets. But this will not work by default. Using a macro to temporarily unprotect the sheet and protect it again will do the trick. But this requires the password being displayed in the VBA code. So please have in mind that this technique only works for scenarios where you want to prevent accidental changes with the password protection.
Read on for the process.
Comments closedStephanie Bruno has updated a useful tool:
If you’re like me, building a data model in Power BI is an iterative process. Sometimes you have to try out different ways of writing measures before you hit on the one that’s right. You end up with temporary measures that don’t actually end up being used in visuals. You may also pull in more columns than you might end up needing, just in case. When you’ve finally finished your masterpiece with measures and visuals, there are probably quite a few that you don’t need. Two problems with this are that having extraneous columns and measures (1) can slow down your model and (2) can make it more difficult to maintain. You may also want to know where on your report a change to a measure will have an impact.
Click through for a demonstration of the solution.
Comments closedMatt Allington wants to trim the fat:
I have a saying in Power BI. Load every column you need, and nothing that you don’t need. The reason for this advice is that columns can make your data model bigger and less performant. You will of course need some columns in your data model for different purposes. Some are used for defining measures and some are used for slicing, dicing and summarising your data in the various visuals. But it is very common for people to load everything from the source, meaning that some of the columns are likely to be loaded but not used. Once the data model is ready and the reporting is done, it can be beneficial to remove the columns that are not being used and are not likely to be used for ad hoc reporting in the near future. The question is – how do you find the columns not being used? This is where Imke’s Power BI Cleaner tool comes in; I will show you how to use it below.
Read on for Seven Minute Abs for your Power BI data model.
Comments closedImke Feldmann solves a problem:
When you use the UI to import files from SharePoint, you’ll end up with the Sharepoint.Files function. This function can become fairly or super slow when you use it on large SharePoint sites. This is due to the fact, that it will retrieve metadata for ALL files that lie on the site. Meaning: The root site whose URL you have to enter as the function argument. So I’ve developed a better way for File import from SharePoint.
Click through for the solution and how to use it. Imke reports 2X query performance when reading Sharepoint data, so it’s worth checking out.
Comments closed