Press "Enter" to skip to content

Category: Power BI

Reverse Engineering the Key Influencers Visual in Power BI

Published 2019-05-02 by Kevin Feasel

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.”

Comments closed

Mobile Reports in Power BI

Published 2019-05-02 by Kevin Feasel

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.

Comments closed

Searching For Text Across All Columns in Power BI

Published 2019-05-02 by Kevin Feasel

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.

Comments closed

Power BI: NFL Draft History

Published 2019-05-01 by Kevin Feasel

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.

Comments closed

Cancelling Power Query Refreshes

Published 2019-04-29 by Kevin Feasel

Imke Feldmann shares how you can stop a data refresh in Power Query without losing the work you’ve done in the designer:

If you’re working with large data or complex queries that take a long time refresh, cancelling one of those refreshes can even take longer time, especially, if the query has run for quite some time already.

Luckily, there is an easy trick to cancel refresh without loosing the work you’ve done already

Read on to see how.

Comments closed

Controlling Power BI Visual Visibility

Published 2019-04-25 by Kevin Feasel

Matt Allington shows how we can take one Power BI visual and use it to control the visibility status of another visual:

I have written a few articles in the past that toy with the ideas of changing visibility and text colour based on selection.  I started to wonder if it was possible to make a visual appear (or not) based on a selection from the user.  There is no out of the box way to do that today. It is possible to use bookmarks to show an hide an object, but the user must click a specific button to do this. I want the user to be able to interact with a report and see (or not see) a chart based on some valid selection across the report.  Microsoft is already working on building expression based formatting across the breadth of Power BI however as of now the only item you can change is the header in a chart.

Hopefully this gets better over time.

Comments closed

Running Spark MLlib to Feed Power BI

Published 2019-04-24 by Kevin Feasel

Brad Llewellyn shows how you can take Spark MLlib results and feed them into Power BI:

MLlib is one of the primary extensions of Spark, along with Spark SQL, Spark Streaming and GraphX.  It is a machine learning framework built from the ground up to be massively scalable and operate within Spark.  This makes it an excellent choice for machine learning applications that need to crunch extremely large amounts of data.  You can read more about Spark MLlib here.

In order to leverage Spark MLlib, we obviously need a way to execute Spark code.  In our minds, there’s no better tool for this than Azure Databricks.  In the previous post, we covered the creation of an Azure Databricks environment.  We’re going to reuse that environment for this post as well.  We’ll also use the same dataset that we’ve been using, which contains information about individual customers.  This dataset was originally designed to predict Income based on a number of factors.  However, we left the income out of this dataset a few posts back for reasons that were important then.  So, we’re actually going to use this dataset to predict “Hours Per Week” instead.

Check it out. And Brad’s not joking when he says the resulting model is terrible. But that’s okay, because it was never about the model.

Comments closed

Power Query Container Size and Performance

Published 2019-04-24 by Kevin Feasel

Chris Web looks into what changing the Power BI Dataflow container size does for us:

Currently there is no way to change this 256MB in Power BI Desktop or Excel although someone has already posted a suggestion on the Ideas site to allow us to change it. How much of an impact does this actually have on refresh performance though? Without the ability to change this setting it’s hard to say, but I suspect it could be significant and that a lot of Power Query performance problems could be explained by this behaviour.

The situation is different in the Power BI service, where I understand there is a limit on the overall amount of memory that a single Power Query query evaluation can use.

Read on to understand the differences here between running on Power BI Desktop and running in the Power BI service, as well as a bit of testing on Chris’s part.

Comments closed

Aggregating Only Visible Values in DAX

Published 2019-04-24 by Kevin Feasel

Alberto Ferrari shows how you can calculate a sum based only on the visible values and ignoring the missing cases:

These measures produce the correct figures month by month. However, at the year level the number of working days might be too big whenever there are incomplete months – this always happens before the end of the year, or when the first month with sales is not January as in the following example. As you can see in the following figure, in this case the yearly value of SalesPerWorkingDays is lower than the value of any month, which does not make much sense.

Click through for the solution.

Comments closed