How to use Data Driven Settings to Control Power BI Reports
It is easy to look at Power BI dashboards as static, but you can use some old school techniques to provide the ability the generate dynamic content using data-driven global settings that will be available to all your report developers. These techniques will allow your published reports to remain unchanged but allow the reports to dynamically reflect default information shown to the user, such as Current Month, Report Comments and even a time stamp of the last data source update.
This all sounds good, but why do you need this? The following solution covers four scenarios I have found these techniques to came in handy. The following screen capture shows a portion of a report utilizing these features. These include;
Commentary Explaining Results
Some solutions require comments that change over time. Information that provides specific details or explains the results shown in the report. Think of these as the first thing you will get a call on when someone looks at the report. Use these to provide alerts and notifications of detail in the data that you need to draw into focus.
Having notes for end users can help in your data visualization solutions’ adoption and usage. You can put these on a report, however having these as data-driven allows you to maintain without having to edit and republish the report.
When was the data source Refreshed?
Being able to have a timestamp in your dataset shows the last time that the source data was refreshed. Power BI data store will refresh to that data source, but that time is when Power BI was refreshed to the source. The Report will refresh to the Power BI data store, but that is not the data refresh, but the visualization’s refresh date and time. Having a data source refresh timestamp in the source allows you to provide further context into the numbers being presented whereas the other timestamps can be misleading.
Current Month & Time Drivers
Various solutions and reports require the tracking of the current month or other default time-based information. Having the ability to always show the current month, not based on the current day or month, allows timing differences between the actual current day, and when your reporting periods close.
We have a data model that has a Calendar or Time dimension. The key is to have the Commentary table joined to the Calendar (Time) dimension at the same level that you want to have the information displayed. You can have a Time dimension at a daily granularity, have another column that has at a Year/Month level, and use this to join the tables.
There are 3 tables involved in the solution, Time which is the Calendar table. The granularity is by day, but we will use the Month to drive the commentary housed in the Commentary table. The Global Variables table contains the key values used in the solution. Setting values in this table can control settings in all data visualizations and reporting solutions using this data. You can use these values not just the current Power BI workbook, but ETL, Excel reports or other tools can leverage this solution.
The following elements are highlighted below;
The MonthFullYear (1) is a column in the Calendar table. As pictured above, this becomes part of the daily granularity of the time dimension but will show the same comment for the entire month, even if the month level is selected.
This is an example of this solution, but if you need it, you could have a different comment for each day. In order to maintain this at a daily level, you join to the Calendar table at the daily level and have a comment at the daily level. If you do not have a comment, then your filter on the report will show a blank value.
Pictured below are the table values in the Calendar table. The key columns are the MonthFullYear(1) which drives the time values, the MonthYearSort (2) which is the sort column used in the MonthFullYear column on reports, and the CurrentMonthYear flag (3), which is populated by a calculated column which looks up values from the Global Variable tables, shown below. Changes to the Global Variables are changed manually or in an ETL process.
The MonthFullYear is the column used to join the Calendar to the Commentary table to drive when the comments are shown. The commentary table was sourced from Excel and is pictured below. The Report field becomes a filter on reports shown later in this section.
The Current Month value used in the reports is driven by a value in the Global Variables table. This table has various setting and values that are used in the reporting solution. The CurrentYearMonth (1) controls the calculated column CurrentMonthYear Flag in the Calendar table (3), pictured above in the calendar table. The DateofLastUpdate (3) drives a time stamp on the reports.
Formulas used in the Solution
The Excel table has the following Formula,
DateOfLastUpdate = “Data as at : ” & TEXT([@DateValue], “MMMM D, YYYY hh:mm AM/PM”).
Power BI may try and change values from excel. Best to use the Excel TEXT function to set the value.
The Calendar table has a function that will look up each row in the calendar table, and display a “Y” value if that row is part of the current month.
CurrentMonthYear = if(‘Calendar'[MonthFullYear] = LOOKUPVALUE(GlobalVariables[TextValue],GlobalVariables[CurrMonthYear],’Calendar'[MonthFullYear]),”Y”,”N”)
Linking Comments to the Reports
When looking at the report segment at the top of the article, the Commentary is a Power BI Card visualization. The Display Field is “Comment” and a Visual Level Filter of Report = “Employee Audit” is added.
Whenever the report selects the timeframe that has commentary, the visual will be populated with the text from the Commentary table. You can have many entries, and filter by Report Name in order to drive different reports. Notes can also be used utilizing the same framework.
Simply change the values in the database to change the values on the reports.
Show Current Month by Default
Using the formulas and set up above, you simply have a filter on the Page that states that the CurrentMonthYear column = “Y” as is pictured below.
Final Report Sample
The picture below shows the final report. As each month goes by, the Commentary will update and the current month will be shown, without any changes to the report. The architecture provides these values to all developers who use the source system and can also be a basis for ETL processes and administrative tasks in the back end.
You can leverage your data model to add in various administrative and operational data elements that you can use to drive your reporting solution. This can limit the number of changes you need to make to reports as time changes or provide a common source for information or settings that various developers will use to provide a more unified reporting solution within your organization.