5 More Power BI Tips

I’ve had this blog post in my mind to write for the past month or so and I’m finally just getting around to it while I’m waiting to board my flight back home.

Check out my first 5 Power BI Tips here

These are just five more Power BI tips and tricks that I think everyone should know in order to get the most out of Power BI and produce better, more useful, and more powerful Power BI reports for their users. So without further ado, here are five more Power BI tips in no particular order. Enjoy!

Create a Calculated Measure to Display Descriptive Tooltips

In last month’s update to Power BI Desktop, customizable tooltips were added to Power BI giving us the ability to add additional measures to a tooltip for a data point in a chart as seen here.

image

This is great when I need to add additional context to something like a bar chart or column chart through a measure.

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:

image

But we can take this calculation a step further to make it a little bit more user friendly. What happens if there are 50 distinct values? That could be a really long, ugly tooltip. Let’s build in some logic to dictate what happens if there are more than a defined number of items (three in this example) listed in the tooltip calculation:

Classes =
VAR ItemCount = DISTINCTCOUNT(‘Grade data'[Class Name])
RETURN
IF(ItemCount >= 3,
CALCULATE(CONCATENATEX(TOPN(3,VALUES(‘Grade data'[Class Name])),’Grade data'[Class Name],”, “))&” and more…”,
CALCULATE(CONCATENATEX(VALUES(‘Grade data'[Class Name]),’Grade data'[Class Name],”, “))
)

This way if there are more than three classes for a given subject, the tooltip will not be ridiculously long.

image

So that’s pretty useful, but we can also take these calculated measures and use them to display our filter values as sort of a header in the report. Just use a Card visualization to show your tooltip calculated measures, as seen here.

image

Use Custom Visuals

One of the coolest parts about Power BI is that the Power BI product team open sources the visuals stack. This allows members of the community to develop custom visuals to be used within your reports. And even better, you can download a whole host of custom visuals from the Power BI Visuals Library!
image

These amazing custom visuals can really expand the usability of your Power BI content and increase the value of your data.

I thought I’d share with you a couple of my favorite custom visuals I use a bunch with my customers.

Box and Whisker Chart

The Box and Whisker Chart is a great visual for displaying basic statistical information regarding a dataset such as first and third quartile, mean, median, and more. My customers have found this visual very useful.

image

Smart Filter by SQLBI

This is an awesome custom visual that allows a user to key in value that should be used to filter a report. All a user has to do is start typing and the intellisense will display the values of the field that begin with the letter the user has typed. This is a fantastic slicer to use for quantitative fields with a large number of values.

image

In the example below, I’m using the Smart Filter to filter the values displayed in the Box and Whisker Chart.

image

 

Use Power BI Template Files to Speed Up Report Development

A really nice feature included in the May update to Power BI Desktop is the ability to save a Power BI Desktop file as a Power BI Template file (.pbit). This file includes the complete definition of the Power BI Desktop file including the model, queries, and visuals. Then when a user opens the file to create a new Power BI report, Power BI Desktop forces the user to save as a new file thus preserving the integrity of the Power BI Template file.

For example, one thing that every Power BI report needs is a Calendar or Date table. So I created a Power BI Desktop file that already includes the Calendar table. This way I don’t have to recreate it every single time I begin creating new Power BI content.

image

To make this a template for other users (and myself) to use, I simple save the Power BI Desktop file and in the Save as type drop down select Power BI Template File (*.pbit). Then you have your Power BI Template to use to speed up the creation of your next Power BI report.

image

Add Synonyms to Improve the Q&A Experience

Synonyms are used in Power BI to improve the Q&A experience for users browsing dashboards within the Power BI service. Basically the Synonyms feature allow us to specify additional terms as “synonyms” for a table or field within a table. For example, a Class may also be known as a Course, but if the user types in the term “Course” into Q&A, no field will be found because the field is called Class Name. If we specify Course as a synonym for Class Name, when a user keys in the term “Course”, Power BI will understand that the user means “Class Name”.

Synonyms can be defined in Power BI Desktop on the Relationships view. Just click the Synonyms button in the Modeling ribbon and select a table for which you want to define Synonyms.

image

Then we can create synonyms for the fields in the Synonyms tab on the right. In the below example you can see I’ve define a few synonyms for some of the fields.

image

So now when I’m using Q&A and type “grade by course” into Q&A, Power BI understand I mean “grade by Class Name”.
image

Create Buckets for Continuous Values

Sometimes you may have a requirement to allow users to filter based on continuous values like a course grade, dollar amount, or age of a person. Theoretically, you could have an infinite number of values for a series of continuous value and this isn’t going to look great as a slicer, as seen below. If a user wants to filter the students based on those students with grades between 80 and 90, they’re not going to have a very good experience selecting every single value in that range.

image

A way to ease the user experience is to create a calculated column featuring a series of discrete values we’ll call “buckets”. These buckets will allow us to group the continuous values into larger buckets that are easier to use as a filter. There’s a few different ways we could approach this, but one of the easiest is to create a new column in your query using the Query Editor.

After you open the Query Editor for the table that has the continuous values, go to the Add Column ribbon and click Conditional Column.

image

With the Conditional Column editor we can specify the column name to use in our condition, the operator, the value for the condition, and the output. Don’t forget to specify the new column’s name and the Otherwise value down at the bottom. The Conditional Column editor allows you to specify values based on different conditions like an IF statement.

image

And in fact, if you look at the underlying M query, you’ll see that’s exactly what’s happening. We’re just using a GUI to write the IF logic for our conditional column.
image

When you’re done we have a brand new column called “Grade Buckets” with our buckets. You’ll also notice that in this particular example, I created an additional Conditional Column to use a sort key for the Grade Buckets.

image

Click Close & Apply to apply your changes to the new query. Set the Sort By Column property for your new buckets field and hide the sort key.

Now we’ve got some much nicer values to use in our slicer that makes life easier for our users!

image

Resources

Read more about May’s Power BI Desktop update here: https://powerbi.microsoft.com/en-us/blog/power-bi-desktop-may-update-feature-summary/

Check out the Power BI Custom Visuals Library: https://app.powerbi.com/visuals/

Check out my first five Power BI tips here: //sqldusty.com/2015/08/28/5-tips-for-powerbi/

Feedback?

Let me know what you think about these tips! Leave a comment down below and if you found them useful, share them with your colleagues. Thanks for reading!

23 thoughts on “5 More Power BI Tips”

  1. Wow talk about efficiency. I was messing with Power BI last night and this article answers three distinct questions I was having. Thanks Dustin !!

  2. Appreciated the “first 5” article, this is a great follow up. Would there happen to be a sample data set to work through this solo?

    1. There are tons of great data sets for you to play with available on the internet. I personally love to use Wikipedia to find great datasets but also data.gov. Data.gov has a ton of awesome data for playing with. Just browse through the data catalog and find something: http://catalog.data.gov/group

  3. Thanks Dustin.

    Btw have you loaded out the date PBIT for others to be able to get?

    Dave

  4. Hi Justin,

    I have just come across your site and have found a lot of great help. One thing I have noticed is that you are using Education Data in the examples above and that is my sector but unfortunately you do not come across any examples. Do you have the example workbook thgat I can have a look through by any chance.

    Thanks

    Chris

  5. Hi Dustin,

    Some very useful tips there, thanks a lot!! Do you happen to know how to surpress the langitude and longitude data if you use tooltips on a map visualization?

  6. Hi,
    For the below measure
    Measure 2 =
    “Sales for the year ” & MAX(Tableacolumn2) &” is down by about 10% when compared to the Previous Year. “& CALCULATE(CONCATENATEX(TOPN(2,VALUES(tablebcolumn1),[Sales]),”,”)) &
    ” are the top selling categories & account for 37% of total sales”

    instead of getting the values, am getting the blank spaces from the CONCATENATEX function, am getting the result as shown below,

    Sales for the year 2017 is down by about 10% when compared to the Previous Year. ,, are the top selling categories & account for 37% of total sales.

    Any one have idea on this.

Comments are closed.