DAX Templates

Gallery for Collection of relevant measures, tables, and columns generated from DAX.

DAX is easy to learn but difficult to master.

For this reason the DAX Quick Templates project has been started. The core of this project is to enable usage of DAX statements across multiple models. We know that each data model will contain unique assets. However, DAX statements can be generalized. Thus, tooling can be built to allow users to easily add measures into

Who is this for?

Business Users

• Beginners

  • Users who are starting to learn DAX
  • Might need a to add DAX templates into their data model but don't understand all the concepts of filter context
  • Individuals who are going to use pre-build DAX templates

• Advanced Users

  • Users who are looking to create their own templates for internal or sharing templates
  • Understands the more advanced features of DAX
  • Users who are looking to create templates to remove repetitive work from creating the same DAX measures over and over again

Developers - Individuals who are focused on developing robust data models in Power BI Desktop - Understands DAX and how to write complex DAX formulas for Tables, and Measures

Contributing to the Project

Thank you for checking out the Repo. This project is only a success by the community building and creating amazing templates.

To begin contributing to the project. Please read the following getting started.

Getting started

1. Users wishing to create templates will need to begin by installing DAX Generator created by Didier Terrien. This can be found in the Business Ops External tools Installer.

2. Once the Business Ops tool has been installed on your PC. Add the DAX generator external tool using the following steps:

   • Open Business Ops
   • Click on the Add External Tools nav item on the left navigation pane
   • Scroll down to find the name Didier Terrien
   • Click on the Checkbox next to DAX Generator. This will install the external tool into your power BI desktop application.
   • be sure to Restart Power BI Desktop as External Tools are only added during the start up process of Power BI Desktop

3. Create a DAX Template using DAX.powerbi.tips

   • Be sure to include as much information as possible. The template description is particularly important to let users find and understand your template.
   • Best practice is to provide some sort of link to a document / blog article that illustrates how to use your measure.

4. Once the DAX template has been made download the DAX template

5. Test the DAX template by adding it into the DAX Generator

   • Open DAX Generator from the Power BI External Tools ribbon
   • Drag the downloaded *.qdt.json file into DAX Generator. This will create a file inside DAX generator that can be used in the data model.
   • Enter your parameters and test that the DAX template is working correctly. DAX Generator documentation

6. Fork the DAX Templates github repo

7. Add your Quick DAX Template into the git repo and create a pull request to add your template

Submissions

Please only submit *.qdt.json files into the templates folder.

Note: In November 2021 a major change was made to the folder structure. This was done to simplify the discover and integration of qdt files into external tools.

Learning folder

This folder is designed for sample template files that can be used as examples for users to learn how to use quick DAX templates. It is recommended to house *.qdt.json files and sample PBIX files that help illustrate how to apply the DAX template files.

If you feel you have content you wish to contribute to the learn folder please consider adding a copy of the *.qdt.json file into the templates folder. This enables the community to have a single place for all templates.

template folder structure

template
│
└─── Topic1 (aggregations per category, math operations, text, time, or totals)
│   │
│   └─── *.qdt.json
│   └─── Logical Folder Grouping (if needed)
│       │   *.qdt.json
│   
└─── Topic2 (aggregations per category, math operations, text, time, or totals)
│   │
│   └─── *.qdt.json 
│   └─── *.qdt.json 

Please Format the *.qdt.json

In order to make the quick DAX templates more human readable please format the JSON files.

If you are using Studio Code to open the *.qdt.json files you can use the short cut key Shift + Alt + f to automatically format the json file.

It is recommended to open JSON files with Studio Code. If you don't have this program please download it here.

Creating a Pull request

This describes the process of adding your designed DAX templates to this repo.

1. Create a Fork of the DAX-Templates
2. Complete your code changes in your forked branch
3. Push your branch up to github and create a pull request between your forked branch and dax templates main

Proprietary Information

Any templates that are being submitted with any company specific or information deemed to be proprietary will not be accepted.

Please, if you are submitting DAX templates and examples use generalized data and naming conventions.

Got Ideas?

I'm sure you have some great ideas for improving this tool and other templates. Please put those thoughts inside the Discussions Area.

If you feel you have found a bug or an issue with the DAX templates or with the tools please submit your issues in the Issues page.

Note: Before making an issue please do a search to see if there is a similar issue that already exists.

Core Concepts

The idea of a DAX Template comes from the following example. Image you have a simple DAX formula like the following:

Sum of Sales = SUM( financial[Sales] )

From this simple measure we can infer that the Sum of Sales measure is creating a sum of the Sales Column from the Financial table. A Sum of a Column is a common occurrence when writing DAX.

By deconstructing this DAX statement we can build common data model elements.

One could parameterize the following items of the DAX statement:

1. Name of the Measure
2. The Column name ( using it's full Address 'table'[column] syntax )

Therefore we could rebuild this measure into a generalized template such as:

Parameter1 = Column Name = Sales
Parameter2 = Table Name = financial

Now, rewriting the DAX statement we can use these newly defined parameters.

Sum of Parameter1 = SUM( Parameter2[Parameter1] )

This statement has now been generalized and can be quickly added to any data model. All the user would need to identify is the table name and the column name.

A More Complex Example

Now image a new example where one wants to create a measure, then use the measure to create a Summarized table.

The following DAX statements would be use to do the following:

1. Create a new measure that counts the number of rows in a table.
2. Creates a new DAX Summarized table where the original measure is used to produce a

Count of Rows = COUNTROWS( 'TableName' )
NewTableName = SUMMARIZE( 'TableName', 'TableName'[Products], "Count of Observations", [Count of Rows] )

By using parameters these two DAX statements can be created into a parameterized template.

The parameters would be similar to the following.

Param1 = "Count of Rows"
Param2 = "TableName"
Param3 = "NewTableName"
Param4 = "Products"
Param5 = "Count of Observations"

Then the resultant two DAX statements would now be produced as the following:

Param1 = COUNTROWS( 'Param2' )
Param3 = SUMMARIZE( 'Param2', 'Param2'[Param4], "Param5", [Param1] )

From this you can imagine any number of different DAX measures that could be produced into templates.