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 the Current Month, Report Comments, and even a timestamp of the last data source update.

Scenarios

This all sounds good, but why do you need this? The following solution covers four scenarios. I have found these techniques to come 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.

Notes

Having notes for end users can help your data visualization solutions’ adoption and usage. You can put these on a report; however, having these as data-driven allows you to maintain them without editing and republishing the report.

When was the data source Refreshed?

Having a timestamp in your dataset shows the last time 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 tracking 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.

Architecture

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 you want to display the information. You can have a Time dimension at daily granularity, have another column that has 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 in the current Power BI workbook but ETL, Excel reports, or other tools that can leverage this solution.

The following elements are highlighted below;

Part of the data model that powers the dynamic report information

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 the Calendar table at the daily level and have a comment at the daily level. If you do not have a comment, 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 settings 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.

Power BI Card’s Filters to set up this report’s commentary.

Show Current Month by Default

Using the formulas and setup above, you have a filter on the Page that states that the CurrentMonthYear column = “Y” as is pictured below.

Page level filter that results in the Current Month being the default timeframe

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.

Conclusion

You can leverage your data model to add 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.