DAX generator native templates documentation

Warning : this documentation may mention some features under development which will appear in short term.

DAX templates are created by experts to be used by non experts and also by experts saving a lot of time. They can be documented to let the user know how to implement the DAX expression and which next steps to achieve. Templates can be easily used by newbies with the integrated wizard.

DAX generator uses these templates to generate DAX expressions which might be measures, calculated columns, calculated tables, calculation groups, KPIs, translations and more.

Templates can be stored in different locations :

  1. In a DAX expression so the template is saved within a model
  2. In a .qdt file stored locally or on the network
  3. In a .qdt.json file stored in a GitHub repository

This documentation concerns only cases 1 and 2

DAX generator keywords are always uppercase. Blue strings can be modified freely by templates authors.

Standard templates

A template starts with a DAX expression including the expression name.

YourQuickMeasure = COUNTROWS( 'YourTable' )

As the user will be able to modify some elements (here the measure name and the table name), the best practice is to make these elements unique and descriptive. Ex : ‘_YourCalendarTable’.

Template information

‘//DG_DEFINITION:’ starts template’s definition. This line and next ones will not appear in the generated DAX expression.

//DG_DEFINITION: ==================================================================================
//DG_DESCRIPTION:Template description
//DG_AUTHOR:Author's name
//DG_WEBSITE:Related blog post URL or author's website

DAX expression kind and name

The kind of the generated expression can be MEASURE, COLUMN, TABLE. More kinds will be added in the future.  
It is followed by the expression name which must be exactly the same than in the DAX expression beginning the template.
Then comes a description used in tool tips and in the wizard.

//DG_OBJECT:MEASURE:VourQuickMeasure:Name of the measure DAX generator will create

Parameters

Authors may create as many parameters as they want.
Parameters kinds are TABLE , TABLE&COLUMN , COLUMN , MEASURE , ANY. More kinds will be added in the future.
It is followed by the parameter name which must be exactly the same than in the DAX expression beginning the template.
Then comes a description used in tool tips and in the wizard.

//DG_PARAMETER#1:TABLE:YourTable:Table we want to count rows (click to modify)

Instructions for end users

Instructions are shown in DAX generator screen and in the wizard. It is the best place to describe the template and steps to achieve. It might be followed by next steps after the DAX expression generation, best practices, …
Authors are free to refer to their website, social media, trainings, …

//DG_INSTRUCTIONS:Type your instructions here
//DG_INSTRUCTIONS:   Add more lines for more instructions

Advanced templates

Advanced templates use different methods than the standard ones. They involve different workflows and some dedicated pieces of code within DAX generator. They are not so flexible than standard templates but they are much mode powerful. For example, they can iterate through all columns in a model and generate a DAX measure depending on some conditions. They can also iterate through variables within a DAX expression or through visuals within a Pbix report.

Template method

A method defines the behavior of DAX generator against a template. By default, the standard method is applied. As a method requests some specific development, it is dedicated to a specific task. In order to create a new method, please contact us in GitHub discussions or through the contact form.

//DG_METHOD:METHOD_NAME

Include a DAX expression

Instead of typing a DAX expression within the template, authors can ask the user to select an expression from their model.
Expressions kinds are : MEASURE, CALCULATED_TABLE, CALCULATED_COLUMN

//DG_INCLUDE_BODY:MEASURE:YourMeasureName:Name of the measure you want to use

Blocks

Blocks are pieces of code used in complex methods. Their use is defined by the method. For example, it can be a DAX expression for a calculation item.

//DG_BLOCK#1:CALCULATION_ITEM:Here is the DAX expression for a calculation item
YTD =
    CALCULATE (
        SELECTEDMEASURE (),
        DATESYTD ( 'Date'[Date] )
    )

Different ways to create a template

My best .qdt template editor is VS Code but you may use any other text editor. In VS Code, I recommend you install one of the DAX extensions. Then, you can click on ‘Plain Text’ in the right bottom corner and click on ‘Configure file association’. Select ‘DAX’ (sometimes you will find it at the bottom of the list).

  1. Click on the + button in the menu and select ‘Add template from model’ then select a DAX expression. An simple template file is created. It is initialised with some instructions and a minimal template. Select the new template and click on ‘Open template in VS Code’ menu.
  2. Open a featured template and click on ‘Copy to user templates folder’
  3. Create a template from scratch or get it from an other user
    • The file location must be ‘C:\Users\YourUsername\Documents\Power BI Sidetools\DAX generator templates\User templates\YourSubfolder‘.
    • The extension must be .qdt