Data Bear – Power BI Training and Consulting

Call us Today! (020) 8720 6880 | info@databear.com

3 hidden tricks in Power BI

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:

  1.  Measures on rows in matrix
  2.  Custom date formatting in modelling view
  3.  DAX Formatting

3 Hidden tricks in Power BI pic

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.

Normal Matrix

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.

Format visual        Show on row

This transforms the view of your matrix considerably.  Now it’s much easier to determine what figures go with a particular month and measure.

Matrix with measures on rows

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.

Date formatting options

 

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.

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.

Modelling tab properties

 

Scroll down to the Formatting section. Then change the format from Date / Time to Date.

Set formatting to date

Next, you will choose your actual formatting.  Choose the custom option.

Choose custom formatting

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.

Custom date formatting

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 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:

DAX Formatter after

To this:

DAX Formatter after

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.

Leave a Comment

Your email address will not be published. Required fields are marked *