Storing Large Images in Power BI

Chris Webb shows us how to store a large image in Power BI:

Jason Thomas and Gerhard Brueckl have both blogged on the subject of storing images as text inside a Power BI dataset:
http://sqljason.com/2018/01/embedding-images-in-power-bi-using-base64.html
https://blog.gbrueckl.at/2018/01/storing-images-powerbi-analysis-services-data-models/

Since they wrote those posts, however, Power BI has added the ability to set the Data Category property on measures as well as columns in tables. This means it is now possible to have the output of a DAX measure displayed as an image in a Power BI report and this in turn opens up a lot of new possibilities – including the ability to work around the maximum size of a text value that can be loaded into Power BI (see my previous blog post for more details) and therefore work with larger images.

I don’t understand why they make this so complicated. I have a Grafana dashboard widget that I show in a Power BI dashboard and have it scaled way down so it fits in under 32K. I appreciate Chris’s answer but that’s a lot of work to show an image.

Power BI Connection String Capitalization

Slava Murygin ran into a problem with capitalization on connection strings with Power BI Report Server:

The message actually says:
“Several errors occurred during data refresh. Please try again later or contact your administrator.”
SessionID: 1b80301e-3898-417a-af9c-2e77ec490728
[0] -1055784932: Credentials are required to connect to the SQL source. (Source at SQLServerName;DBA_Pro.). The exception was raised by the IDbCommand interface.
[1] -1055784932: The command has been canceled.. The exception was raised by the IDbCommand interface.
[2] -1055784932: The command has been canceled.. The exception was raised by the IDbCommand interface.

In my case the cause of the problem was very silly thing. PowerBI Server assigned only one data source connection string to my report, while in my report I had two data sources with only the difference in a Database Name capitalization:

This was a weird scenario.

Getting Prior Year’s Year-To-Date with DAX

Kasper de Jonge takes a look at how to calculate a prior year’s year-to-date over the same period as the current year:

Well maybe.. what happens here is that the DAX engine took the whole date range we have in context and shifts it back 12 months. This means for year 2019 it will use January first to December 31. So we get the entire year, is that what we want? Or do we want to see the sales for the previous year until the day we have data for this year so we can compare? Both need different DAX so let’s take a look.

Read on for a detailed analysis, including where you might go wrong.

Using Power Query to Expand Out Missing Dates

Matt Allington solves a problem in Power Query:

Suppose you have data in the form of dates (not consecutive) with a value for each of the dates (see the table below left side). You need to expand the rows of the table (create the missing rows) so that you will have all the consecutive dates in the given range and each of the dates has the previous updated value (see the table below right side).

The solution has a pretty large number of steps but is straightforward.

Power BI Error with R Packages

Imke Feldmann takes us through a workaround for an interesting error:

When running R-scripts in Power BI, I got all sorts of error-messages who all had one thing in common: They were complaining about one or more packages being installed by an R version with different internals.

Click through for the solution. I’m not sure I’ve run into this issue before and I’d rather keep it that way.

Reverse Engineering the Key Influencers Visual in Power BI

Chris Webb learns how the Key Influencers Power BI visual works, including some interesting undocumented functions:

A fascinating insight into how Power BI works, but is this any practical use to us? Let me be clear: I don’t think you should be using any of these functions yourself in a real-world report. I’m sure all this would be documented and publicised if Microsoft did want us to use it ourselves! Another consideration is that these new functions return tables and that makes them awkward to use in regular .pbix Power BI reports – I guess we could create calculated tables although that’s not as flexible as returning a table from a query as shown above. That said, even though we can’t write our own DAX queries in regular Power BI reports, we can write our own DAX queries in Paginated Reports and we can now create Paginated Reports that use a Power BI dataset as a data source. I tested putting one of the queries generated by the Key Influencers visual into a Paginated Report connected to the same dataset and it worked ok (even after publishing). You can also embed DAX queries connected to a published dataset in Excel too, as I show here. Hmm, plenty to think about then…

Chalk this up as “fun to know but not recommended to use yourself.”

Mobile Reports in Power BI

Prathy Kamasani shares some thoughts on what makes for a good Power BI mobile report:

Steadily more users are getting interested in Power BI mobile reports, especially when you design reports for executives; they are more interested in mobile view, of course, they do want to explore the detail data but not always.  Until recently, I was someone who doesn’t pay much attention to mobile reporting unless the user explicitly asks for one. So, when I don’t create a layout for mobile when the user opens the report using Mobile App, it automatically opens the report in landscape mode. Which is fine but it’s not designed for that purpose, it misses the sleek look.

Creating a mobile report is pretty easy in Power BI but creating a good one is not that easy. In this blog post, I want to show how I approach mobile reports in Power BI.

For me, the key is glanceability. People looking at a mobile report typically don’t have fine-grained control mechanisms like a mouse—it’s thumbs on a phone screen (or maybe fingers on a tablet if you’re lucky). This means you don’t want fine-grained controls. Instead, lay things out as simply as possible and don’t make people click on tiny sections of the screen.

Searching For Text Across All Columns in Power BI

Imke Feldmann has an interesting solution to a text search problem in Power Query:

In the native function, you have to pass in a record with search term and column name. So if you search for “blue” in column “Description”, your formula would look like so:

Table.Contains( YourTableName, [Description = "blue"] )

But that’s not what I want in this case. I want the formula to search through all columns within the table for the occurrence of “blue”.

Read on to see how to do this.

Power BI: NFL Draft History

Dustin Ryan gives us a Power BI dashboard covering NFL draft history:

With the 2019 NFL Draft upon us, of course I wanted to visualize some NFL draft data in Power BI. So I put together this interesting set of visualizations based on some data I scraped from Pro Football Reference. The dataset includes drafts from 1936 to 2019 including picks through round 6 where applicable. I’ll update the dataset as the remaining rounds of the 2019 draft are completed. So feel free to take a look, interact with the dashboards, and let me know if you have any questions.

Click through for the dashboard.

From Power BI to Azure Analysis Services

Erik Svensen shows how you can migrate a Power BI data model up to Azure Analysis Services:

After the Azure Analysis Services web designer was discontinued per march 1 2019 – link – there is no official tool to do a move of a PBIX datamodel to Azure Analysis Service. But by using a few different tools we do have ways of doing it anyway.

Click through for the step-by-step instructions.

Categories

May 2019
MTWTFSS
« Apr  
 12345
6789101112
13141516171819
20212223242526
2728293031