A Fine Slice Of SQL Server
Chris Webb explains an error message:
If you’re using DirectQuery mode in Power BI you may occasionally run into the following error message:
OLE DB or ODBC error: [Expression.Error] We couldn’t fold the expression to the data source. Please try a simpler expression..
What does it mean and how can you fix it?
Read on for the answer, as well as a quick depiction of query folding.
Comments closedGauri Mahajan loads Cosmos DB data into Power BI:
As we are going to report Cosmos DB data with Power BI, the two items we need in place are a Cosmos DB instance and well as an installation of Power BI. It is assumed that an instance of Cosmos DB – SQL API is already created with some sample data. It is also assumed that the latest version of Power BI Desktop is already installed on the local machine. One can create some sample data using the built-in scripts in a Cosmos DB instance. One can explore the data using the Data Explorer on the dashboard of the Cosmos DB instance as shown below.
Read on for the process. Stories like this are why I discount the ability of document databases to change fluidly from one document to the next—as soon as you want to analyze things across documents, you suddenly need schema and structure.
Comments closedJames Bartlett solves a tricky problem:
Most of you have probably run into a situation where someone in your organization has authored a report in the Power BI web service, and now they want to make changes that can only be done with Power BI Desktop. So, you try to download the PBIX file from the Power BI web service, only to discover that you can’t, because if it wasn’t created as a PBIX, it can’t be downloaded as a PBIX. Infuriating!
Read on for a solution as well as a Powershell function to make it easier.
Comments closedKristyna Hughes shows how to create an info tooltip in Power BI:
The steps below will walk through how to add an information icon to the report, making a tooltip page containing your additional information, and enabling the tooltip to allow users to hover over the icon and see the information.
This can be quite useful, especially as it gets context information out of the way of users after they don’t need it anymore. That’s important for dashboards you expect people to look at regularly.
Comments closedA relationship in Power BI is often for when we want a table to filter another table (based on the selection of items in the visuals). This filtering happens in the direction of the relationship. Meaning that if DimCustomer has a one-to-many relationship with the FactSales, and the relationship direction is single directional from the DimCustomer to FactSales, then DimCustomer can filter the FactSales, but not the other way around.
What we see a lot in the Power BI models is that the developer changes the relationship to both-directional so that each of the tables can filter the other table. This looks like a nice feature, but it will come at a big cost.
Click through for five good tips in blog format as well as written form.
Comments closedChris Webb walks us through some inconsistencies:
A few months ago one of my colleagues at Microsoft, David Browne, showed me an interesting Power Query problem with how the Xml.Tables and Xml.Document M functions handle null or missing values. I’m posting the details here because the problem seems fairly common, it causes a lot of confusion and it’s not easy to deal with.
In XML there are two ways to represent a null or missing value:<a></a> or omitting the element completely. Unfortunately the Xml.Tables and Xml.Document M functions handle these inconsistently: they treat the <a></a> form as a table but the other as a scalar.
Click through for an example and a workaround for the issue.
Comments closedMarco Russo explains why opening some Power BI files might take so long:
There could be many reasons for that, but if you have calculated columns and/or calculated tables in your model, you should be aware that they could be the reasons why this happens. It could be, so I want to explain when this happens.
The short explanation is the following: when you open a PBIX file, Power BI Desktop automatically recalculates those calculated columns and calculated tables that depend on a volatile formula.
Read on for the longer explanation, which includes a (possibly incomplete) list of volatile formulas.
Comments closedMarc Lelijveld is in a sharing mood:
Recently, I have had a challenge at a customer, where a central teams maintains many dataflows in Power BI, to store their only and single version of the truth. However, this central team maintained many different dataflows in a single workspace, but did not want to share the entire workspace with others. What now? How can they share a single dataflows in Power BI?
In this blog, I will describe different ways to share dataflows in the Power BI service and highlight pros and cons of each solution. Read on to find out what options you have, and what my personal preference would be.
Read on to learn why you might want to share a dataflow, as well as four techniques to do it.
Comments closedGilbert Quevauvilliers sets up an infinite loop:
In this blog post I show the final part which is how I created the Power BI report which takes the previous 3 steps and then creates the Power BI Report.
I am going to show you how I got the data in using Power Query and then created the Power BI report.
Read on for the process. But now I want a report to see who has access to the report for who has access to reports. And I think I need a report for that layer. And that layer. And…
(Shh, yes, I know you can get that all from the same report but it’s so rare I get to make a “Turtles the whole way down” reference).
Comments closedMarco Russo and Alberto Ferrari explain one of the most important DAX functions:
CALCULATE, with its companion function CALCULATETABLE, is the only function in DAX that can change the filter context. Its use is very intuitive at first, and most DAX developers start using CALCULATE without knowing the most intricate details of its behavior. Then, sooner than later the use of CALCULATE becomes frightening because CALCULATE starts to misbehave. When this happens, it is nothing but a signal that you need to learn more theory and deepen your understanding of the behavior of CALCULATE.
In this article, we do not introduce the most complex behaviors of CALCULATE. Instead, we provide a beginner’s guide to CALCULATE, and we try to avoid making things simpler than they are. CALCULATE is definitely a complex function. Here we introduce its base behaviors, with a solid theoretical foundation.
Definitely a must-read if you work with Power BI and don’t have CALCULATE() down pat.