Power BI is a fantastic business intelligence & data visualisation tool by Microsoft that can allow you to quickly create professional looking charts from numerous sources of data.
This blog is aimed at you if you have just begun creating reports in Power BI and want to learn some insights to get up to speed quicker or if you have been creating reports for a while and want to discover some simple steps that can make your report writing easier.
1. Useful Measures
If you are user of Excel, then you are probably familiar with Excel formulas. DAX, which stands for Data Analysis Expressions is a way to write formulas and allows you to create Measures in Power BI.
Here are some examples of useful measures that allow you to calculate comparisons between sales revenue over different time periods:
|Total Sales Revenue||= SUM(FactSales[Revenue])|
|Total Sales This Period||= CALCULATE([Total Sales Revenue],DimDate[Date])|
|Previous Qtr Sales||= CALCULATE([Total Sales Revenue], PREVIOUSQUARTER(DimDate[Date]))|
|Revenue YTD||= TOTALYTD([Total Sales Revenue],DimDate[Date],All(DimDate),"31/12")|
|Revenue MTD||= TOTALMTD([Total Sales Revenue],DimDate[Date])|
To find out all the available DAX formulas keep this link handy.
Note: For the examples above, we have a table of sales information called FactSales and a dimension of date information in a table called DimDate, with these tables joined by a relationship on sales date. The value of these measures will change based on the dates selected on the report page.
See this link for more detailed information about time calculations in Power BI.
2. Ordering Month Columns
One thing you probably find yourself doing quite frequently is to summarise data by month. You would normally have the month names on an axis, and what you find is that the ordering of this information is alphabetical, not by the calendar month as you would expect.
To do this...
- Make sure that you have a month number column in your Date dimension
- Click on the month name column
- Go to Modeling > Sort by column and choose the column that stores the month number. This option is useful for any columns that are not sorted using traditional methods such as alphabetical or highest to lowest.
3. Sharing Data Between Reports
If you are writing a number of reports that use similar data & measures, you should set up a shared data set. This allows you to share all this information between reports, saves on time in creating reports and refreshing the data.
To do this...
- You need to create a master report that has all the facts, dimensions and measures that you need, publish this report to Power BI.
- When creating the next report, instead of getting the data from the original source, use the Power BI Service to get data. In this case you connect using Get Data > Power BI Service > Sign in to your Power BI account, pick the report/data set you need.
- If you need to add new measures, change the data structure, you download the Master report, make the required changes, then publish the report again. Once done, all these changes will then be visible to the other reports.
Refreshing the data is done in the same way – using the Power BI Gateway. The good news is once you refresh the Master dataset, all other reports are automatically updated.
Note: Currently you can only share datasets from within the same workspace, so you will need one master dataset for each workspace.
4. Structuring Your Data
Without going in to too much detail it is important to structure your data in a way that is “friendly” to the end user and to Power BI. This allows the end user to find what they need when building reports and allows Power BI to work its magic so that you don’t have to. This means, naming tables, columns and measures in a user friendly way, ensure that any tables that are related have the same column names and data type allowing Power BI to pick up on the relationships and correct sorting. When building measures, it is best to create “building block” measures that you can use in your other measures. See above for how we have used the Total Sales Revenue measure in the other measures.
5. How to Create One Report to be Used by Multiple Customers
When you start creating reports in Power BI, you will immediately notice how quick and easy it is to generate beautiful and useful charts. What then tends to happen is your users and customers will start demanding more and more reports. Very quickly you end up doing the same thing over and over again, and what was a joyful experience becomes monotonous and repetitive. You then realise that there must be a better way.
What if I could create one report and give that to all of my customers?
You probably will struggle with this concept because if all your customer data is in one place, when you do that, your customers will be able to see each other’s information.
Enter, row level security or RLS.
With this, using Power BI Desktop go to Modelling > Manage Roles. Here you can create a role for a customer where you specify how the role maps to data in the report by placing filters on appropriate tables.
Eg. On a Client table that is related to your Fact tables put it an expression such as:
[ClientID] = UserPrincipalName()
UserPrincipalName() returns a result that could be anything. It could be a Client ID for the currently authenticated user, or it could be the email address of the logged in user. This will depend on your method of integration with Power BI. You can use the Power BI Service to integrate/authenticate and map your web application users to their data, or Azure AD authentication to allow the Power BI Portal to surface the appropriate data for the logged in user.
We have created a number of portals that integrate with Power BI and achieve the above result, so if you want more information about how to do this, please get in touch.