Power BI is such a powerful tool, but every week that goes by makes me realize that I haven’t come close to realizing it’s full potential! Every week I learn new things about this tool that make my client’s lives (as well as my life) easier. In this blog I want to highlight some of the random/cool Power BI features that I used this week to deliver for our clients.
- Understanding Direct Query’s Limited Functionalities
- Report Page Tooltips
- Sorting Columns by Custom Field
- Column Distribution, Quality, and Profile Views in Power Query
- RANKX Use Case
#1. Limited Functions Using Direct Query
I know this doesn’t seem like a feature that saved me, but understanding it absolutely saves me time. I have recently run into several instances when a solution seemed to be within reach, but then the use of Direct Query snatched defeat from the jaw of victory. Because of the different functionality between Import and Direct Query modes, make sure that your end solution will be possible considering the data import method that you use.
For those unfamiliar, when importing data into your Power BI data model, you are given two (technically three, but for this example, two) options on how to bring in your data; Direct Query or Import. When using Direct Query, your report will directly query the data source when rendering calculated columns and measures (Tableau equivalent of Live Connection). Using Import will create a snap shot of your data and store it to run queries off of instead of going back to your data source.
Bringing your data into Power BI using Import greatly increases the flexibility that you have in manipulating your data. There are a few important limitations that you should be aware of:
- Date hierarchies will not be brought in by default. When using Import, date hierarchies will be recognized by Power BI and categorized as such automatically
- Time Intelligence function are greatly restricted and the time intelligence functions normally available in Quick Measures will not be there.
- Calculated Columns are only allowed to calculate at the row level, so if you are trying to aggregate data using the CALCULATE function, you will get an error that the function is not support in Direct Query mode.
#2. Viz in Tooltips Are So EASY in Power BI
I recently used viz in tooltips (or Report Page tooltips as they’re referred to in Power BI) for a client, and I found it so useful as part of their end solution.
The thing that makes report page tooltips so intriguing in Power BI is that they are so easy to implement, and they are extremely customizable. They also offer the ability to see the next level of detail in any of your chart elements without having to actually drill through to another tab. I highly suggest trying them out in your next Power BI development project!
Check out the example report below to see an example of report page tooltips.
#3. Sorting Column By Custom Field
This is a work around that I probably use more than any other in this list of Power BI features. If you are wanting to sort a bar chart, table, or any other chart element in Power BI you will need to have that field present in the visualization pane. The only issue is, if you don’t want your sorting field to be part of the visualization, you will need somewhere to place that field that won’t show in the chart.
The work around is to place the sorting field in the tooltips area! In the screen shot to the right, I have the visualization pane of a bar chart that I am using to portray countries by new cases of a disease. Instead of sorting by new cases, I want to sort by population. To do this I will drop the population field into the tooltips section as shown on the right. This will allow me to see this field in the Sort options. The only downside is that this field will then show in my tooltips, but I also have a work around for that!
Once I’ve put my sorting field in tooltips, click the three dots at the top of the chart you wish to sort and you’ll see the field as one of your options. I can now see Population as a sorting option in the screenshot to the right.
The one downside to this technique is that you will now see that field in the tooltips when you hover. My solution to fix this is to create a custom tooltip using the previous section, Report Page Tooltips, to get only the fields that you want to see in your tooltip.
#4. Column Distribution, Quality, and Profile Views in Power Query
I often explain what Power Query is to Tableau users by saying that it’s like having Tableau Prep embedded within the Tableau Desktop application. Although this is not an exact explanation, it’s one that I found to make the most sense especially since I had used Tableau Prep a lot before using Power BI. One of my favorite things about Tableau Prep is the visual displays of your data and the workflows as shown below.
One of the features that I recently discovered is that Power Query offers some similar meta data features. If you go to the View tab within Power Query you can check the Column Quality, Column Distribution, and Column Profile boxes which will give you some information about the quality and characteristics of the data in your data set. The Column Quality box will allow you to see how many values in your column are Valid, in Error, or Empty. The Column Distribution box will create a distribution of the values in the column and will tell you how many distinct and unique values are contained in the column. The Column Profile box will give you more advanced statistics and distribution information in a pop out section at the bottom of the page.
I have found these features to be very helpful in gaining insight to my data during data cleaning and preparation in Power Query.
#5. RANKX Use Case
Although RANKX is a relatively straight forward DAX function, I have found a couple of use cases for it that are extremely powerful. First, let’s walk through the basic syntax of the function.
First, you will define what table of data you are ranking. Use this part of the formula to set the filter context like in your other calculations (ex. ALL, ALLSELECTED). Next, choose the expression that you wish to evaluate (ex. SUM(value), AVG(value)). The remainder of the inputs are optional. You can determine whether your rank is displayed in ascending or descending order and how to handle ties in rankings.
// Rank = RANKX(Table, Expression, [Value], [Order], [Ties])
In the formula below, we add some filter context to the data set that we are filtering. The table we want will include only those rows that have the max date in the date field. Since there are other filters applied to the report page, using the ALLSELECTED() function will ensure those are taken into account as well. We then use a calculation that I had already made that had created our desired metric.
// Rank = RANKX( FILTER( ALLSELECTED('owid-covid-data'), MAX('owid-covid-data'[date])='owid-covid-data'[date]), [Total Deaths], ,DESC )
I recently leverage RANKX to find the difference in a value between the most recent and second most recent date values. These are, however, were not linear and were in the data in random intervals. I ranked the dates using RANKX and then created my calculate function to sum their values when the rank field = 1 and rank field = 2. This allowed me to find the difference in values regardless of the gap between the two dates.
Thanks so much for reading! Do you have more questions about Power BI? Talk to our expert consultants today and have all your questions answered!