Data Bear’s Monday Motivation Blog Post
Today I will show you some awesome tricks that will truly change your experience in Power BI to make your life much easier when creating reports. These 3 hidden tricks in Power BI are things that I did not know about while working daily on Power BI, I was showed them by either other blogs (which I will share with you) and by other Power BI users. I now use these almost daily.
3 Hidden tricks in Power BI explained below:
- Measures on rows in matrix
- Custom date formatting in modelling view
- DAX Formatting
Trick 1: Measures on rows in matrix
Did you know you can display your measures in a matrix in a row? What a game changer. Below is the default view when setting up a matrix visual.
There are 2 Measures added in the below matrix. By default, they get added to columns.
This view will, for many instances, be perfectly fine, but if you have many measures to view, you will have to scroll which spoils the experience and is unnecessary. Change the following setting.
Under the formatting menu for the matrix, go to the Values drop-down, scroll down and switch “Show on Row” to On.
This transforms the view of your matrix considerably. Now it’s much easier to determine what figures go with a particular month and measure.
Trick 2: Custom date formatting in modelling view
The next one of the hidden tricks in Power BI refers to date formatting. I have always found this to be a pain because you only had a few formats to choose from.
In the past, I’ve had to format dates myself, which will then be a text format, and made my life very difficult when it can to sorting. This next trick completed eradicated that problem for me.
If you have a date that is in Date / Time format.
The format you want is Jan 2015, but this does not exist in the default options available. This is where it gets exciting.
Go to the modelling view of your report. Click on the field on the right of the screen. This will open the properties pane of the field.
Scroll down to the Formatting section. Then change the format from Date / Time to Date.
Next, you will choose your actual formatting. Choose the custom option.
This will open up a further little menu for Custom Formatting. Simply choose an option or add your own. You will see an example of your option appear.
Great! Now you have a formatted date that still identifies as a date and you can easily sort on this field. Thanx to Thys for showing me this trick that was shared by SQLBI.
This trick compliments DAX calendar tables.
Trick 3: Formatting DAX
Third on my list of hidden tricks in Power BI is how to format your DAX. Watching and reading a variety of content about Power BI, I’ve heard the authors mention a few times, “please format your DAX”. I did not know what is the correct way was to format my DAX, nor did I have the time to study it. I did not see the point, because my code was working. That being said, if your DAX is long and complicated, it does get very difficult to read, if it’s not formatted somewhat. I stumbled across this awesome tool by SQLBI. This DAX Formatter does all the work for you.
It formats a piece of DAX code from this:
It is as simple as copy and paste.
Hoping these tricks will make your development experience much easier as they do for me.
Learn more about Power BI by taking one of our courses.