Model Documentation and automation with DAX Scripts in Tabular Editor

If you haven't noticed, I do love Tabular Editor, documentation and everything that can be done automated and consistently through scripts or API's.

As your semantic model grows in complexity and number of measures, you often would like to see the dependencies or edit and maintain multiple measures in bulk. In Power BI Desktop or normal Expression Editor, we focus on one measure at a time.

1) DAX Script - a single document with all your DAX code

DAX Script is a single document where we can collect and edit all our DAX code. DAX scripts can be saved as text files, using the .te3daxs file extension and support the following DAX objects:

• Measures (including KPIs)
• Calculated columns
• Calculated tables
• Calculation groups (including calculation items)

If you have an existing model, you can right-click the Model in the TOM explorer and select 'Script DAX'. Then you have a single document with all DAX code for above mentioned objects.

Search and replace works great in this document :)

2) DAX Script - Generic for calculation groups

Furthermore, we can resuse generic DAX scripts that create calculation groups

Two calculation groups I often use are Time Intelligence and Rounding figures (too see figures either in current, in thousands or in millions).

Let's create that using below DAX script:

-----------------------------------------
-- Calculation Group: 'Time Intelligence'
-----------------------------------------
CALCULATIONGROUP 'Time Intelligence'[Time Calc],
    Precedence = 1

    CALCULATIONITEM "Current" = SELECTEDMEASURE( ),
    Ordinal = 0

    CALCULATIONITEM "YTD" = TOTALYTD( SELECTEDMEASURE( ), Calendar[Date] ),
    Ordinal = 1

    CALCULATIONITEM "PY" = CALCULATE( SELECTEDMEASURE( ), SAMEPERIODLASTYEAR( Calendar[Date] ) ),
    Ordinal = 2

    CALCULATIONITEM "PY YTD" =
        CALCULATE( TOTALYTD( SELECTEDMEASURE( ), Calendar[Date] ), SAMEPERIODLASTYEAR( Calendar[Date] ) ),
    Ordinal = 3

    CALCULATIONITEM "YoY" =
        VAR _current = SELECTEDMEASURE( )
        VAR _py = CALCULATE( SELECTEDMEASURE( ), SAMEPERIODLASTYEAR( Calendar[Date] ) )
        VAR _yoy = _current - _py
        RETURN
            IF( OR( ISBLANK( _current ), ISBLANK( _py ) ), BLANK( ), _yoy ),
    Ordinal = 4

    CALCULATIONITEM "YoY%" =
        VAR _current = SELECTEDMEASURE( )
        VAR _py = CALCULATE( SELECTEDMEASURE( ), SAMEPERIODLASTYEAR( Calendar[Date] ) )
        VAR _yoy = IF( NOT ISBLANK( _current ) && NOT ISBLANK( _py ), _current - _py )
        VAR _result = DIVIDE( _yoy, _py )
        RETURN
            _result,
    Ordinal = 5


----------------------------------
-- Calculation Group: 'Figures in'
----------------------------------
CALCULATIONGROUP 'Figures in'[Calc]

    CALCULATIONITEM "Current" = SELECTEDMEASURE( )

    CALCULATIONITEM "In 000's" =
        IF(
            RIGHT( SELECTEDMEASURENAME( ), 1 ) = "%" || SEARCH( "Title", SELECTEDMEASURENAME( ), 1, 0 ) > 0,
            SELECTEDMEASURE( ),
            DIVIDE( SELECTEDMEASURE( ), 1000 )
        )

    CALCULATIONITEM "In mio" =
        IF(
            RIGHT( SELECTEDMEASURENAME( ), 1 ) = "%" || SEARCH( "Title", SELECTEDMEASURENAME( ), 1, 0 ) > 0,
            SELECTEDMEASURE( ),
            DIVIDE( SELECTEDMEASURE( ), 1000000 )
        )        

3) DAX Script - Generic automation for model documentation with INFO.VIEW functions

Or even further, utilizing the new INFO.VIEW DAX functions - we can combine a automatic updated model documentation with one table with all information on measures, tables, columns and relationships. Credit goes to Fernan.

A standard Power BI report page could then be created based on the generic model documentation. It's amazing what possibilities with INFO.VIEW functions open up for.

I'm a fan ??

-- Credit to Fernan --
-- https://www.youtube.com/watch?v=jTjk_moZAEg --

------------------------------------------
-- Calculated Table: 'Model Documentation'
------------------------------------------
TABLE 'Model Documentation' =
    VAR _columns =
        SELECTCOLUMNS(
            FILTER(
                INFO.VIEW.COLUMNS( ),
                [Table] <> "Model Documentation" && NOT ( [IsHidden] )
            ),
            "Type", "Column",
            "Name", [Name],
            "Description", [Description],
            "Location", [Table],
            "Expression", [Expression]
        )
    VAR _measures =
        SELECTCOLUMNS(
            FILTER(
                INFO.VIEW.MEASURES( ),
                [Table] <> "Model Documentation" && NOT ( [IsHidden] )
            ),
            "Type", "Measure",
            "Name", [Name],
            "Description", [Description],
            "Location", [Table],
            "Expression", [Expression]
        )
    VAR _tables =
        SELECTCOLUMNS(
            FILTER(
                INFO.VIEW.TABLES( ),
                [Name] <> "Model Documentation" && [Name] <> "Calculations"
                    && NOT ( [IsHidden] )
            ),
            "Type", "Table",
            "Name", [Name],
            "Description", [Description],
            "Location", BLANK( ),
            "Expression", [Expression]
        )
    VAR _relationships =
        SELECTCOLUMNS(
            INFO.VIEW.RELATIONSHIPS( ),
            "Type", "Relationship",
            "Name", [Relationship],
            "Description", BLANK( ),
            "Location", BLANK( ),
            "Expression", [Relationship]
        )
    RETURN
        UNION( _columns, _measures, _tables, _relationships )



---------------------------
-- Measure: [# of Measures]
---------------------------
MEASURE 'Model Documentation'[# of Measures] =
    CALCULATE(
        COUNTROWS( 'Model Documentation' ),
        'Model Documentation'[Type] = "Measure"
    ),
    Description = "
COUNTROWS('Model Documentation')",
    FormatString = "#,0"

--------------------------
-- Measure: [# of Columns]
--------------------------
MEASURE 'Model Documentation'[# of Columns] =
    CALCULATE(
        COUNTROWS( 'Model Documentation' ),
        'Model Documentation'[Type] = "Column"
    )

-------------------------
-- Measure: [# of Tables]
-------------------------
MEASURE 'Model Documentation'[# of Tables] =
    CALCULATE(
        COUNTROWS( 'Model Documentation' ),
        'Model Documentation'[Type] = "Table"
    )

-------------------------------
-- Measure: [# of Relationship]
-------------------------------
MEASURE 'Model Documentation'[# of Relationship] =
    CALCULATE(
        COUNTROWS( 'Model Documentation' ),
        'Model Documentation'[Type] = "Relationship"
    )        

I'm a fan - and let's reuse these scripts on all semantic models.

Anything I'm missing or we need to add?

Enjoy ??

Thank you for reading my article, hope you enjoyed it. Comment or provide some input is much appreciated.

?? Hi my name is David Kofod Hanna, Senior Advisor and Consultant at twoday and my aim is to guide self-service Power BI developers for more enterprise managable concepts in a consumable and practical way - because I have been on that journey for years, and still is...