A Fine Slice Of SQL Server
Are you curious what Power BI Premium Per User is all about? Adam walks you through how to get it and what it means from a user experience. Take advantage of Power BI Premium features without the Premium capacity price!
Click through for the video as well as a few links for more info.
Leave a CommentErik Svensen has nothing to hide:
When you connect to a Power BI Dataset from Power BI desktop you might have noticed that you can see and use hidden measures and columns in the dataset.
But the hidden fields cannot be seen if you browse the dataset in Excel.
But that does not mean that you cannot use the fields in Excel – and here is how you can do it.
Read on for a few techniques.
Leave a CommentGilbert 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.
Leave a CommentChris 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.
Leave a CommentAlberto 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.
Leave a CommentPhil 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.
Leave a CommentPaul 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.
Leave a CommentAdam 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.
Leave a CommentImke 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.
Leave a CommentStephanie 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.
Leave a Comment