A Fine Slice Of SQL Server
Chris Webb shows off multi-value parameters in Power Query:
Why is this interesting? In the past, Power Query parameters were always single values like a date or a string; now a parameter can contain mutliple values.
There’s one other new feature in Power Query Online that goes along with this: In and Not In filters, which can use these new List parameters.
Click through for some examples.
Comments closedGilbert Quevauvilliers writes some Powershell:
In this blog post I am going to show you how to use PowerShell to run a DAX query from my dataset, and then store the results in a CSV file.
I will also include the PowerShell code!
I really liked the awesome blog post by Kay on the Power BI Team which you can find here: Announcing the public preview of Power BI REST API support for DAX Queries
Read on to see what prep work you need to do, as well as the scripts needed to pull this off.
Comments closedKristyna Hughes wants to update an existing schema:
Goal of this demo: Update a Power BI model schema by adding a column to the data model without opening a PBIX file and ensure the scheduled refresh still works.
Why would this be useful? Updating the schema in the desktop tool requires an entire refresh of the data model which can take a while if your model is large. Also, app developers could systematically add new elements to existing data models using a formulaic XMLA script through SSMS, saving your report designers time when new fields need to be added.
Read on for limitations, as well as the process.
Comments closedNick Edwards wants to perform a lag on data in Power Query:
I’m sure you’ve all heard of windowing functions in SQL Server? But what if you need to replicate these windowing functions in Power Query?
Here I have a sample CarSales.csv dataset of car sales per brand, per year, per month and the number of units sold. I will use this dataset to demonstrate how we can replicate the windowing LAG function in Power Query.
Click through to see the process.
Comments closedMarc Lelijveld wants to swap a connection from using Azure Analysis Services to Power BI Premium:
Having the context of an Azure Analysis Services dataset that is migrated to Power BI Premium, you might have to rebind many reports. Especially if this dataset is positioned as being a managed dataset that is also used for self-service purposes and has many related reports.
In this blog I will elaborate on how you can easily rebind all these reports to the new Power BI dataset, without downloading all reports and manual rebinding.
It’s not a trivial operation, but it is a lot easier than updating each entry individually.
Comments closedSoheil Bakhshi is starting some 101-level training on Power BI:
Many people talk about Power BI, its benefits and common challenges, and many more want to learn Power BI, which is excellent indeed. But there are many misconceptions and misunderstandings amongst the people who think they know Power BI. In my opinion, it is a significant risk in using tools without knowing them, and using the technology is no different. The situation is even worse when people who must know the technology well don’t know it, but they think they do. These people are potential risks to the businesses that want to adopt Power BI as their primary analytical solution across the organisation. As a part of my day-to-day job, I communicate with many people interacting with Power BI. Amongst many knowledgeable users are some of those who confuse things pretty frequently, which indicates a lack of understanding of the basic concepts.
So I decided to write a series of Power BI 101 to explain the basics of the technology that we all love in simple language. Regardless of your usage of Power BI, I endeavour to help you know what to expect from Power BI. This is the first part of this series.
Read on for the start of this series, asking the question “What is Power BI?”
Comments closedWhen you double-click on a cell in an Excel PivotTable, you invoke the drillthrough feature of the PivotTable which shows the underlying data for that particular cell. This feature was initially designed for Multidimensional databases in Analysis Services. In a Multidimensional model, it is also possible to add different drillthrough actions that can be activated through the context menu in Excel. While the customization of actions is not feasible for a Tabular model, the drillthrough feature is active by default. It returns all the rows visible through the filter context in the table that includes the measure definition. In many scenarios, this default behavior does not provide a result consistent with the data computed in the result that you see visible. Through the Detail Rows Expression property of a measure in the Tabular model, you can customize the drillthrough behavior in Excel, thus controlling the rows and columns returned to the user.
This article explains how to use the Detail Rows Expression property to customize the Excel drillthrough behavior for a PivotTable connected to a Power BI dataset or an Analysis Services database – they can be identified as Tabular models in the remaining part of this article.
Read on for a demonstration, plenty of explanation, and even some elucidation.
Comments closedTeo Lachev implements a workaround for drillthrough in Power BI Paginated Reports:
Instead of drillthrough report actions, implement URL-based actions. Assuming embedding for your organization, here are the high-level steps:
It’s definitely not as pretty, but as Teo shows, it is functional.
Comments closedMarc Lelijveld has an image for us:
Often I got the question from customers: “Can you assign my workspace to a premium capacity?” But frequently they actually do not really need Power BI Premium. It remains to be a difficult topic to decide whether someone needs Power BI Premium or not. Therefore, I decided to setup a decision tree that helps to decide if you need Power BI Premium or not.
This decision tree highlights a bunch of Premium specific requirements and features like breaking the data size limits, XMLA Endpoints, unlimited content sharing and much more!
Click through to see that decision tree, though note that it does not differentiate between Premium and Premium per User.
Comments closedGilbert Quevauvilliers wants to spread the blame around:
I recently got a request asking if it was possible to have a different contact person for each report or dashboard within a single Power BI App.
The good news is yes, this can certainly be done, and I will show you below how I got it working.
Read on to see what the normal situation looks like, and then how to set it per report.
Comments closed