The Power BI team recently announced the release of the Pulse Chart custom visual. Excited by its potential, I decided to show you today how to define dynamic events, so your Pulse Chart will always trigger popups with top events, no matter how you slice and dice the report.
For example, in the dashboard that we will create today, you can view different popups of top Facebook posts, when you filter the report by US Presidential Candidate (Clinton or Trump or both), by Facebook reactions (Like, Love, Wow, etc.) or by time period.
You can try it! Click on Clinton or Trump below, select one of the reactions, and click on the Play icon of Pulse Chart to see different posts based on the data you sliced.
Isn’t it awesome?
Subscribe to DataChant below to get access to the Power BI Desktop file (.pbix) that I used to create this report, or contact me at gilra@datachant.com if you need help with such report for your Facebook brand.
[rad_rapidology_inline optin_id=”optin_5″]
Before we start the tutorial, let’s highlight the challenge at hand. The trivial approach to feed the Pulse Chart is to use a fact table in which a textual column is empty for most of the rows, but contains a meaningful description for few of the rows. This textual column will be used as the event description of the chart, and will populate the event popups. Here is an example of a fact table with the Event Description column empty for most of the rows, and filled only for the significant rows that represent the events on the Pulse Chart. The table was taken from Microsoft official sample here.
You can use Power Query to create an Event Description column (e.g. using the Add Custom Column) with non-blank event description when a certain numeric column is high enough. But this approach will yield zero events once you start filtering your report.
So, how can we define the events in a dynamic way that will popup events, no matter how thin we slice it? The answer is DAX.
To start this tutorial, download this workbook. It contains 999 Facebook posts and reactions for each of the presidential candidates’ Facebook pages. The workbook is not refreshable. The refreshable version is still a trade secret 🙂
Get the Data
Open Power BI Desktop. Click on the Get Data drop down menu and then click Excel.
In the File dialog, select the workbook that I have shared above. In the Navigator window select the tables Candidates, Posts, Reactions and ReactionTypes. When you are done, click Load.
Optional Step
In the next step, we will define a new column for the event descriptions. It is not a mandatory step for this tutorial. You can use the original message as the column that will feed the event description. In this step we’ll add the candidate name as a prefix to the post message, so when Trump posts “Make America great again”, we will have the text: “Trump: Make America great again”.
Click Edit Queries, select the Posts query, and then click Add Custom Column in Add Column tab.
In the Add Custom Column window, type Event in New column name, then copy & paste the formula below (without the equal sign) and click OK.
= [Candidate] & ": " & [Message]
Note: In this step we could go further with the trivial static approach, merge between the Posts and Reactions tables, and fill this new column with blank values if the the count of reactions is not high enough. But this approach will not yield the dynamic popups that we seek for.
Click Close & Apply on the Query Editor.
The Data Modeling Phase
In the next step we’ll create a calendar table, and define all the relationships. Then we will add few DAX measures that will explained later on.
In the Modeling tab, click New Table, and copy & paste this formula:
CalendarTable =
CALENDARAUTO ()
You can now define additional calculated columns like Year, Month & Day. We will skip this step, as it is not directly related to our topic today.
Now, in the Relationships view, define the following four relationships:
- CalendarTable – Date –> Posts – Date
- Candidates – Candidate –> Posts – Candidate
- Posts – Post ID –> Reactions – Post ID
- ReactionTypes – Reaction –> Reactions – Reaction
Here is the Reactions table as viewed from the Data view. The URL column will help us to create a cool Chiclet Slicer. Chiclet Slicers are not our main topic today, but they make the entire experience just awesome, so please bear the distraction 🙂
Here is the Candidates table, as viewed from the Data view. The URL column will help us to create another cool Chiclet Slicer.
Select the Reaction column, expand the Sort By Column drop down menu, and click Index. This step will help us to see the reactions by their order of appearance in Facebook, and not by their alphabetic order.
Adding the DAX measures
Open the Report view, click the ellipsis icon near the Reactions table and click New measure.
Repeat the previous step and copy and paste each of the formulas below in the measure edit box that is highlighted below. After you paste each formula press Enter to apply the new formula.
Sum of Reactions =
SUM ( Reactions[Reactions] )
Top Post Reactions =
CALCULATE ( [Sum of Reactions], TOPN ( 1, Posts, [Sum of Reactions], DESC ) )
Top 10 Threshold =
CALCULATE (
MIN ( Reactions[Reactions] ),
TOPN ( 10, ALLSELECTED ( Reactions ), [Top Post Reactions], DESC )
)
Custom Message for Event =
CALCULATE (
FIRSTNONBLANK ( Posts[Event], 1 ),
TOPN ( 1, Reactions, [Top Post Reactions], DESC )
)
Event Message =
IF ( [Max Reactions] >= [Top 10 Threshold], [Custom Message for Event], BLANK () )
Let’s wait a bit with the explanation of the DAX measures above, and continue with the walkthrough for now.
The Cool Slicers Phase
You can skip this phase, and later on apply the native Visual level filters in your report. I decided to describe this step as it provides a cool slicing and dicing experience, that better illustrates the need for dynamic events in the Pulse Chart.
Import the Chiclet Slicer from here, and create the Reaction Types slicer below by dragging and dropping the fields according to this screenshot.
Create the Candidates slicer below, by dragging and dropping the fields according to this screenshot.
Import the Time Brush from here, and create the timeline slicer below by dragging and dropping the fields according to this screenshot.
One last visual before the Pulse Chart
Let’s also add a Table element that will help us to understand the sliced data better later on, and will allow us to navigate to the actual posts on Facebook.
Click on the Table visual, and create the Table visual below by dragging and dropping the fields according to this screenshot.
To set Post URL as a hyperlink, select the Data view, click on the table Posts, and select the column Post URL. Now change Data Category in the Modeling tab to Web URL.
Customize the table by activating the URL icon. This will show the Post URL as a link icon.
Finally, the Pulse Chart
Import the Pulse Chart from here. Click on the Pulse Chart icon in the Visualization pane, and drag and drop the field Date from CalendarTable to the Timestamp box.
Why do we use Date and not DateTime? It’s a matter of taste.
You can see in the screenshot below the Pulse Chart with the Date as a Timestamp. Only the daily top post will be displayed on the graph.
If we set the DateTime as the Timestamp, the The Pulse chart will get cluttered, as the graph don’t group multiple events in a single day.
Next, drag and drop the measure Top Post Reactions to the Value box. As a result, the graph will get its Y value from the number of reactions of the top post per day.
That’s it. We are done. You can now slice and dice the report by candidate, reaction types and dates and see how your selections affect the graph, and which different events are popped up.
You can subscribe to DataChant Blog below and to get the prepared .pbix file that we have just created. Continue reading if you wish to understand the DAX formulas that we have used.
DAX Explained
Sum of Reactions =
SUM ( Reactions[Reactions] )
The above measure is quite obvious. It summarizes the number of reactions, and will adjust the result by its filter context.
Top Post Reactions =
CALCULATE ( [Sum of Reactions], TOPN ( 1, Posts, [Sum of Reactions], DESC ) )
Here we sum up the reactions of the post that received the maximal number of reactions. We use the CALCULATE function with a filter context of TOPN that help us to keep only the top post in the calculation. It behaves like a MAX function, but a bit differently.
Why didn’t we use MAX?
Since each post has 6 rows in the Reactions table (one for each reaction type), and since the reaction type LIKE gets the highest reactions for each post (most of the users click the LIKE button rather than the other buttons), the MAX function will not work properly when none of the reaction types are selected in the Reactions slicer. Using MAX will show the number of Likes that the top post received, and not the total of reactions. So slicing by Like will have the same effect as not selecting any reaction type.
Top 10 Threshold =
CALCULATE (
MIN ( Reactions[Reactions] ),
TOPN ( 10, ALLSELECTED ( Reactions ), [Top Post Reactions], DESC )
)
The Top 10 Threshold measure helps us to define the minimal number of reactions that are needed for a post to qualify as one of the top 10 posts. The arithmetic expression defines the minimum of Reactions in table Reactions. The filter context gets the top 10 posts.
Note: When none of the reactions are selected in the Reactions slicer, you may find more than top 10 events on the graph, because the Pulse Chart shows the superset of all the top 10 events per reaction type.
You may also see less than 10 events on specific selections, because on some days you have multiple posts that reached the top 10 threshold, but we only show the top 1 post in the graph. To change this behavior, and get the entire 10 top events when a single reaction is selected, set the DateTime field in table Posts as Timestamp in the Pulse Chart.
Custom Message for Event =
CALCULATE (
FIRSTNONBLANK ( Posts[Event], 1 ),
TOPN ( 1, Reactions, [Top Post Reactions], DESC )
)
This helper measure is used by the next measure. It returns the event description who got the top value in column Event of table Posts. The TOPN function with its first argument of 1 is used as a filter context to ensure we retrieve the event description of the top post, and not other posts from the same day.
Event Message =
IF ( [Max Reactions] >= [Top 10 Threshold], [Custom Message for Event], BLANK () )
This is the final measure we use to feed the Pulse Chart’s Event Description. It returns blanks when the number of reactions is below the threshold, and returns the event otherwise.
That’s it for today. I hope you enjoyed this tutorial. Please subscribe below, to get the latest updates and a free access to the Power BI dashboard file that we created today.
Pingback: 8 Ways Power BI Falls Short - Or Not (Tableau Series Part 3) - DataChant