Auto Generate Time Intelligence DAX Measures Without Writing DAX In Power BI

Why We Need A No-Code DAX Solution?

Writing DAX measures can be overwhelming and time consuming, specially writing time intelligence ones. I personally had a love-hate relationship with DAX and always looked for smarter ways of writing repetitive functions.

If you share the same pain and want to better your DAX experience, let me show you a way of generating time intelligence measures with a single click of a button, without manually writing a single line of DAX code. Thanks to the free and open source tool BRAVO For Power BI, by SQLBI.

In addition to freeing you from writing DAX manually, here are some additional benefits of using this tool:

• Allows you to add DAX measures to Import, DirectQuery and Live Power BI reports
• Creates a brand new DAX table ‘Date’ to support all time intelligence measures
• The auto generated measures have well formed definition, highly readable format and use of variables make them self documented
• All measures created are organized in folders and subfolders for better discovery

How To Auto Generate DAX Measures With Bravo?

Prerequisites

• Download and Install free Power BI Desktop
• Download and Install free Bravo For Power BI

Seven Easy Steps

Commence below steps when you have a Power BI report with some measures to work on. For better measure discovery and maintenance, create a ‘Measures Table’ to organize all the measures in folders and sub folder. Click here to learn how to create a ‘Measures Tables’ in Power BI.

Step 1: Open the Power BI file in Power BI Desktop and create a required measure. For the demo purpose, consider the below example report with measure ‘Total Sales’:

Step 2: Open ‘Bravo for Power BI’ and click on ‘Attach to Power BI Desktop’:

You will see a list of all open Power BI files, select the one you want to connect to. In this example, I choose ‘Contose Sales’:

Step 3: Once Bravo is connected to the Power BI report, it will offer some features that can be directly applied on the connected Power BI report. Watch this video to know more about it’s offerings.

Look for the option ‘Manage Dates’ in the left pane, if it’s greyed out and throws below error, go to the Step 4, else, Step 5.

Step 4: You need to disable ‘Auto date/time’ feature in the Power BI file, so that Bravo can manage dates for you.

For this, open the Power BI file in Power BI Desktop, go to Files->Options and Settings->Options->Current File->Data Load->Auto date/time and uncheck the option. Close the Power BI Desktop and reopen.

Now, if you refresh Bravo, it should enable ‘Manage Dates’ option for you.

Step 5: Inside the ‘Manage Dates’ window, there are few tabs dedicated to add some functionalities to your Power BI report. For now, let’s focus on tab ‘Dates’, which will create a Date table in the Power BI report. You don’t need to click anything here, just review and continue to next step.

Step 6: Go to ‘Time Intelligence’ tab and select from dropdown, list of measures based on which time intelligence functions will be generated. You can select all the measures in the report, or choose specific ones and click on ‘Preview Changes’:

After reviewing, click on ‘Apply Changes’:

This will update the report model:

Step 7: Open the Power BI report and expand Fields pane from the right side, you should be able to see a new folder ‘Time intelligence’ added to the list, which contains a list of subfolders for purpose specific measures:

Here is an example measure ‘MOM Total Sales’, which was auto generated in the subfolder ‘Growth’:

Feel free to remove any unnecessary measures or reorganize subfolders differently. The ‘Date’ table created can be leveraged for other regular tasks in the model.

Conclusion

To sum up, Bravo is an excellent supporting tool to Power BI development, which can accelerate your DAX development and organize measures in an efficient way.

Found this useful? Let me know your experience with Bravo and DAX in comments below.