Category: Power BI

Patrick LeBlanc explains a confusing part of KPIs in Power BI:

Now, take a look at the KPI visual.  What happened?  First, you should notice a trend line on the KPI that depicts Sales Amount for each month.  This is cool and a great feature of the visual, but wait.  Why doesn’t the Indicator value and the goal match the values in the Card?  Now I see the confusion.

I appreciate that Patrick put in several embedded reports to show us exactly what’s going on.

Devin Knight discusses query folding and the View Native Query feature inside Power Query:

The idea behind Query Folding is to push the logic that you built into a Power BI query back to the data source server and execute it there in it’s native language instead of doing a client side transform of the data.  Why is this important?  Let me give you an example.  Say you have a 2 billion row SQL Server table you need to connect to in Power BI, but you want to filter to only return the last year of data.  With Query Folding the filter of that data is done on the SQL Server side instead of on the client side. If Query folding did not take place than that would mean all 2 billion rows would be brought across the network only to then filtered out on the client workstation.  So clearly the ideal situation is that all your queries get folded for the best possible performance, but Query Folding only works in certain scenarios.

I hadn’t heard the term “query folding” before, but the concept makes sense; in the PolyBase world, it’s “predicate pushdown.”  Check out Devin’s post, as he shows how easy it is to see to what extent your query is running client-side versus server-side.

Dustin Ryan talks about this month’s batch of Power BI updates:

Searchable slicers are also a new feature in the latest release of Power BI Desktop. A couple days ago I wrote about some of my favorite custom visuals, which included the Smart Filter by SQLBI. I think I still prefer the Smart Filter in many situations, but the search-ability of the native Slicer is definitely a nice feature to have right out of the box.

The headline is row-level security, but there are several interesting features here.

Matt Allington takes a pivot table and digs into components to help determine underlying drivers of change:

As you can see below, TV and Video total sales were up $178m vs prior year, yet there was also a decline in sales of $79m caused by lower sell prices.  And Cameras and camcorders actually had an increase in sales due to sell price, and that drove the total result higher than it otherwise would have been.  Of course there is normally an inverse relationship between price and volume (the lower the price, the more you sell).  The trick is to maximise sales (or more correctly margin $).

There are a lot of comments worth reading as well.

Dustin Ryan has a few more Power BI tips for us:

But what if I want to show a descriptive field as a tooltip? As of now, only measures can be displayed in the tooltip. But have no fear! One way to work around this is to create a custom calculation to display my descriptive field.

If I want to show a description of the classes within the subjects seen in the chart above, I can create a calculation like so:

Classes = CALCULATE(CONCATENATEX(VALUES(‘Grade data'[Class Name]),’Grade data'[Class Name],”, “))

So now I can show the classes within each subject as a tooltip

I really like his synonyms example.  Read the whole thing.

Reza Rad shares some tips on building mobile-friendly reports in Power BI:

Report fitted in my mobile screen, however when I see that in smart phone even with 5 inch screen, it is too small! texts are not readable in that size, and bar or column charts are too small to be selected with touch screen. When you design for smart phone size consider making sizes bigger. Also don’t use too many charts in one page, because it will make things small. few charts in each page will makes things readable and user will be able to highlight them and select items.

You can use formatting to make your font sizes bigger, and titles of charts bigger. However there are some charts and some elements that can’t be resized (for example labels inside tree map, or labels for x-axis in column chart below). Make sure to design big and clear with only few visualization elements in each page. Here is what I build and it shows in mobile phone nicely;

The upshot is that dashboards are about where we’d want mobile development to be—easy to use and “just works”—but reports have a ways to go yet.

Chris Webb shows how to create specific numerical and character sequences in Power BI:

It’s also possible to use this technique to create lists of characters. For example, the expression:

{“a”..”z”}

Returns a list containing all of the lowercase letters of the alphabet

There are a few interesting ways of generating sequences in M, some of them (as the first commenter notes) akin to Python’s sequence methods.