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.
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:
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.
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.
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!
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.
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.
In the example below, I’m using the Smart Filter to filter the values displayed in the Box and Whisker Chart.
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.
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.
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.
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.
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”.
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.
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.
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.
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.
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.
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!
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: https://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!
