Power BI June Update

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.

Power BI Row-Level Security

Reza Rad takes a crack at row-level security within Power BI desktop:

Row Level security is about applying security on a data row level. For example sales manager of united states, should only see data for United States and not for the Europe. Sales Manager of Europe won’t be able to see sales of Australia or United States. And someone from board of directors can see everything. Row Level Security is a feature that is still in preview mode, and it was available in Power BI service, here I mentioned how to use it in the service. However big limitation that I mentioned in that post was that with every update of the report or data set from Power BI Desktop, or in other words with every publish from Power BI Desktop, the whole row level security will be wiped out. The reason was that Row Level Security wasn’t part of Power BI model. Now in the new version of Power BI Desktop, the security configuration is part of the model, and will be deployed with the model.

This is a great security feature, so I’m happy to see the Power BI team taking it the next step forward and integrating RLS directly into Power BI desktop.

Disaggregating Reports

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.

More Power BI Tips

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.

KPI Indicators

Devin Knight’s Power BI Custom Visuals class continues:

Change the Banding type property to one of the following:

  • Increasing is better –  Increasing is best when you’re measuring things like sales or profit. If you go over your profit target that’s a good thing!

  • Decreasing is better – Decreasing is probably best when you’re looking at something like budgeting. Staying under budget is usually a good thing. Unless you being too far under budget means you won’t get that money again next year which leads to the last option

  • Closer is better – This is for when you need your data to land in the middle of a bell curve.  Meaning if you go too high or too low that’s a bad thing. This is often useful when looking at medical data.  For example, if your blood pressure is too high then that’s a bad thing, but if you’re blood pressure is too low that’s also a bad thing too. You need to land in the middle somewhere, which is what this option allows.

There’s plenty of good advice here, so check out the video.

Mobile-Friendly Reports In Power BI

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.

Power BI Sequences

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.

Radar Charts

Devin Knight continues his custom visuals course:

In this module you will learn how to use the Radar Chart, a Power BI Custom Visual. The Radar Chart is sometimes is also know to some as a web chart, spider chart, or star chart.  Using the Radar Chart allows you to display multiple categories of data on each spoke (like spokes on a bicycle wheel) of the chart. The Radar Chart does support the display of multiple metrics, which allows you to compare and contrast the “pull” that each category has on your metrics.

I still say you should stick with the fish chart for all of your visualization needs.

Handling Division Failure

Reza Rad looks at how division can go wrong in Power Query:

In this example I just returned zero if I find error. but you can return error message if you like with [Revenue Per Item][ErrorMessage]. This method is great error handling method when an error out of blue happens in your data set. I always recommend using TRY method to get rid of errors that might stop the whole solution to work properly.

I have to mention that steps above are separated to show you how the output of try expression looks like. In fact you can combine both steps above in single step with TRY OTHERWISE as below (Thanks to Maxim Zelensky for pointing this out);

The end result is code which is a bit more complex, but safely handles a number of edge cases.

Hexbin Scatterplot

Devin Knight continues his Power BI visuals series:

In this module you will learn how to use the Hexbin Scatterplot Power BI Custom Visual.  The Hexbin Scatterplot is a variation of the traditional Scatter Chart but instead of using bubble size it relies on color saturation and hexbins to show value distribution.  You should consider using this chart when you’re more interested in visualizing density instead of individuals points themselves.

This is worth checking out.

Categories

June 2019
MTWTFSS
« May  
 12
3456789
10111213141516
17181920212223
24252627282930