In any reporting project, access to data is not enough.
You have to access instantly to its explanations, its insights. Moreover, these insights must be contextual, that means they have to be related to the data they explain. This link between data and insights must be clear, obvious for the human eye, unequivocal.
The commercial director who follows the evolution of sales must be able to viualize in his data when he hired the 3 sales representatives. The marketing director must know when he triggered the 1st promotion campaign on his products. The financial director must be able to visualize accurately how and when acquired companies are consolidated in his group’s scope The HR director must see in a nutshell when his employees received their bonus, and the impacts that it had on the total payroll.
For that, the ideal chart in Power BI is called the Pulse Chart. It is a custom visual, which means it is not in the native list of visuals that exist in Power BI. To use it, you will need to import it. In the visualizations pane, click on the symbol with 3 dots.
Then select the option “import from the Office Store”. Office Store
Once you are arrived on the Office Store, enter « pulse » in the search bar, then, once the visual is proposed, click on « Add ». You imported successfully the visual. You can now use it in order to create graphs with beautiful tooltips.
ToolTips are relative to Date & Company Name
The Pulse Chart visual has locations for its 6 different fields.
All these fields allow a complete customization of the visual, but the result is already excellent with only 3 fields.
For ourselves, we will need to use :
Timestamp (our dates axis, we’ll drag our “Month” Field there)
Values (the field which contains data enabling the creation of the curve, we’ll drag the field “Market Cap Value” there)
Events (the field which contains data enabling the creation of tooltips, we’ll drag the field “Events” there)
Let’s go back to our data model. To feed the field Events with key milestones, nothing really difficult. That could be done using simple conditional columns in Power BI.
An example of rules that could be created by the conditional column :
IF [Date] = June, 29th 2007 AND IF [Company] = "AAPL"
THEN "Apple Founder & CEO Steve Jobs introduces the iPhone"
Let's check that :
Creation of a new custom column
In our data model, the fact table contains a column with the name of several companies, that we will call [Company], and a Date column that we will call [Month].
The idea is to identify some important dates for each company, and then to display the “Key Milestones” to these dates only, for related companies only.
This is a double instruction. Unfortunatly, conditional columns do not support double instructions, but only single ones.
Thus, we are obliged to create a custom column in the query editor.
Syntax is of that kind :
Power Query (M) syntax of the custom column
Just a little "else null" in the end of the code will help us not to have any syntax error.
write "else null" not to have any syntax error
You now have created beautiful dashboard to do some story telling !