Power BI Tips and Tricks: Save hours of developing your report
Even a simple Power BI report development can take hours, not because of complex measures or data model but because of calendar setup, simple measure creations, and format of visualization. In this blog, I will talk about three ways to reduce the developing time and energy significantly and still produce a professional looking report.
TLDR; tell me the 3 ways!
Create or download a MQuery calendar and load it every time
Use Tabular Editor to create basic Measures and even more
Create or download a theme you enjoy and load it
1. Create or download a MQuery calendar
I find Power BI date handling one of the most beautiful but also painful parts of Power BI development. If you want to give drill up/down options to users, want to do fun date calculation, or are dealing with more than one fact table, then you know the value of the calendar table.
Since it’s fundamental to Power BI development, creating a calendar table is probably routine for you. So instead of creating a calendar every time using CALENDAR() or CALENDARAUTO(), and then adding columns, why not create your own calendar with all the columns that you use already created?
It may take some time to develop in the beginning (or not, if you just get somebody else’s work online!) but then it will save you quite some time doing repetitive calendar creation. Trust me, this 15 ~ 20 minutes per report adds up. To learn how to create your own or get the basic code, check out my blog: Power BI MQuery Calendar.
2. Use Tabular Editor to create and organize measures
Tabular Editor is an amazing tool you can use to save a lot of time developing Power BI reports. Although it can be used in so many different ways, if you have never used it before, I recommend starting small. Why not start with creating multiple SUM() measures at once for your metric columns and changing the data format and location of measures without waiting for Power BI to load? Check out this blog on starting with Tabular Editor: Tabular Editor – Power BI Time Saver.
3. Use Power BI Theme - either create your own or use premade
The Power BI theme was the last thing that was on my mind when I was learning Power BI. And yet, it turned out to be a huge time saver! These themes are easy to apply and can instantly make your report look nicer and more cohesive. Additionally, themes are JSON files, and therefore easily shareable among developers; so using the themes can give cohesiveness on reports regardless of developers within an organization or a department.
Option 1. Explore and download themes
You can go to Power BI community Theme Gallery (also accessible by [Views] > [Themes] > [Theme gallery]) and browse for a theme you would like to use here.
If you find a theme you like, download the JSON file that is attached (the extension .json). Make sure the file is located where you can easily access.
For how to apply it, go to the next section “Now I have the JSON file – how do I apply it?”
Option 2. Basic theme creation using a UI
Although limited in its functionality, you can actually create a theme using Power BI UI. Go to [Views] tab, click [Themes]. You will see the option “Customize current theme”. You can use this Microsoft doc to customize the current theme in moderation.
Although this is a great way to start creating your own theme, its capability is limited; if you want more flexibility, you will want to create your own theme by writing a JSON file. Even then, however, I still recommend using this UI to build the general frame of your JSON file.
Whenever you feel ready, click [save current theme] to save the JSON file.
Option 3. Theme creation: JSON file
It is much work at first, but you can create your own theme and use it for all your Power BI reports. For my employer, this is what I did; I started by changing fonts and colors using the UI introduced earlier, and then I customized frequently used line charts and slicer settings. Now, it saves me so much time when creating a new Power BI report and I even shared the file with coworkers so our reports can have coherent themes! Although there is no one hub where you can get all the information you may want for this theme creation, I will leave a few links that I used that helped me create what I want:
***Sometimes when I could not find the correct JSON info, I downloaded a theme that had what I wanted and looked into its JSON file. In case you want to do that, I will leave the Gallery link again here.
Now I have the JSON file - how do I apply it?
In Power BI, go to the [Views] tab and click [Themes]. You will see [Browse for themes]. Click that and find the JSON file.
Conclusion
When you are creating a new report, it takes time to make it polished. With MQuery Calendar, measures using Tabular Editor, and a customized theme, you can now quickly turn your blank canvas into a presentable report. The downside of these are, either you will have to use what others have already created (like, Mquery calendar, Tabular Editor script, or a theme) or you will have to take some time to create your own. I recommend using what others created first and then work your way to creating your own, since that will show you how you want to customize your own calendar, script, or a theme.
Comentários