Power BI Tips & Tricks: Simple Additions with Big Impacts

Let me start this post by stating the obvious: data is the main focus of our data models and reporting. Without the data, well, the models and reporting would be pointless. But as necessary as the data is, often times I find that it’s the small details in our data models and reports that bring a significant impact (hence the last post on capturing detailed metadata in data models). In this post, I want to offer four simple additions to consider in each Power BI solution that you develop. I have found to pay helpful dividends well beyond their simplicity.

Change Log

The concept of a change log probably isn’t new to you, we see them all over in release notes of apps and software that we use daily. I had been working in Power BI for years before a former colleague suggested adding a change log to my reports. When they did, I must admit that the idea had never occurred to me. I hadn’t thought of work in Power BI as creating a software product, but it is. We add new features and functionality to reports, fix bugs, make updates based on user feedback in a similar manner to what we think of in typical software development. With that concept in mind, we need a place in Power BI to track changes and to keep notes for ourselves and other developers that is separate of commenting the code. The change log solves this need.

So, how do you add a change log in Power BI? I typically create a new tab, call it “Change Log”, make the tab hidden (it does not need to be exposed to end users when the report is published), add a text box to the page, and type away! Here’s what I typically like to include in my change log notes:

• Date – I usually like to use the date when I am publishing the changes
• Developer name
• Notes about what I changed, added, or removed
• Reference to a work ticket number (if applicable)
• Name(s) of those who requested the change (if applicable)
• Version number – this is optional. In my day to day work, there are not any official version numbers in reporting, but I like to track it for my own curiosity . If I’m only making small changes or fixing a bug, I’ll consider that a minor version and change the number after the decimal point. Something like v. 1.1. If there are significant features being released, I’ll consider that a major version. v. 2.0. As an example, think about changes to the operating system on your phone as a rule of thumb for the difference between major and minor versions.

If you have a robust change control system in place that allows you to keep detailed notes, a change log may not be necessary. In the case that you don’t, though, this simple addition is really helpful!

Visualize the Data Refresh Date

let
    Source = DateTime.LocalNow(),
    #"Converted to Table" = #table(1, {{Source}}),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Refresh Date"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Refresh Date", type datetime}})
in
    #"Changed Type"

This code uses the DateTime.LocalNow() function to capture the date and time that the query was executed. The code then converts the date and time into a table containing a single column and single row. We need the table to be able to load the data into Power BI. Finally, the code assigns a date/time data type to the column. From there, click “Close & Load” and you’re ready to add the refresh date to a visual.

This approach works well when all of the data in your model is refreshed at the same time. In cases where you have more complex incremental refreshes or are sourcing data that is updated at different intervals, this approach may not work as well. In those cases, you may consider adding refresh dates/times to your data tables. In my experience, these cases are the exception. Regardless of what approach is right, adding the refresh date and time to your model and reports will be a great boost for your end users.

Display Data Sources

Another simple addition that can save a lot of questions in your reporting is to display the data sources. I find this most useful when there is more than one source. I was recently working with a healthcare company that was migrating electronic medical records (EMR) systems and we had reporting with data from both EMRs. Adding the name of the EMR to the report became just as important as the patient data in the report! It was key to the stakeholders to know where that data was coming from in order to take appropriate action once they ingested the reports. Your use case may be more in depth than just “EMR A” or “EMR B” like I did in this example, but your users will appreciate the clarity and transparency of knowing where the data comes from. It can also give you context to add notes on new or unfamiliar data sources or place hyperlinks in the report so users can quickly navigate to the source data.

User Name DAX Measure

The final tip in this article is only applicable when role based or row level security is implemented in a Power BI data model. Since these security models rely heavily on knowing the user who is logged in and viewing the report, you as the developer need to be able to verify the credentials being used to login to Power BI. The USERNAME() and USERPRINCIPLENAME() DAX functions give us the ability to see these credentials. Simply create a new DAX measure and choose one of these functions. Neither require any additional arguments.

• USERNAME() displays DOMAIN\username
• USERPRINCIPLENAME() displays the user’s primary email address

Visualize the measure in your report and viola, you can see who is logged into that instance of Power BI! I have found this very helpful to verify which set of test credentials I have logged in to test a model with. The measures also prove beneficial when troubleshooting issues during user acceptance testing of the model or once pushed into production.

In Closing

I trust that these tips will help add some quick wins to your Power BI solutions. What other simple additions have you come across?