Creating alias columns for calculation groups

By 2 commentsTags: , ,
Please download the PBIX for this post 🙂

Quick Summary

  1. To add an alias column to a calculation group, first define an appropriate alias calculated column with DAX. Duplicate values are allowed in this column (if desired).
  2. Use Tabular Editor to set the Group By Columns property of the alias column to the original calculation item column.
  3. Set the Sort By Column property of the alias column to be the original Ordinal column (if desired).

Introduction

The purpose of this post is to show how to add an “alias column” to a calculation group, containing alternate names for calculation items but functioning in the same way as the original calculation item column (the column containing the names of the calculation items).

To begin with, did you know you can add calculated columns to calculation groups in the first place? Since calculation groups are not loaded from a data source via Power Query, only DAX calculated columns can be added to a calculation group.

It is often useful to have alternate names for values in any table (not just calculation groups). Common examples outside calculation groups are short/long names or translations in dimension tables.

I will start with the simpler scenario of adding columns to a calculation group for filtering and grouping, then discuss alias columns.

Calculated columns for filtering and grouping

For this post, I have created a semantic model containing a Time Intelligence calculation group which originally contained the columns Time Calc and Ordinal (download PBIX here). I have added four further calculated columns to be used for filtering and grouping:

  • Time Calc Category classifies each calculation item as “$” or “%”.
  • Time Calc Category Order is a hidden sort-by column for Time Calc Category.
  • Management Report is a flag, equal to 1 for items to be included in the Management Report.
  • YTD Report is a flag, equal to 1 for items to be included in the YTD Report.

The Tabular Editor DAX script for these calculated columns is:

-------------------------------------------------------------
-- Calculated Column: 'Time Intelligence'[Time Calc Category]
-------------------------------------------------------------
COLUMN 'Time Intelligence'[Time Calc Category] =
    VAR TimeCalc = 'Time Intelligence'[Time Calc]
    RETURN
        IF ( CONTAINSSTRING ( TimeCalc, "%" ), "%", "$" )

-------------------------------------------------------------------
-- Calculated Column: 'Time Intelligence'[Time Calc Category Order]
-------------------------------------------------------------------
COLUMN 'Time Intelligence'[Time Calc Category Order] =
    VAR TimeCalc = 'Time Intelligence'[Time Calc]
    RETURN
        IF ( CONTAINSSTRING ( TimeCalc, "%" ), 2, 1 )
    Visible = FALSE

------------------------------------------------------------
-- Calculated Column: 'Time Intelligence'[Management Report]
------------------------------------------------------------
COLUMN 'Time Intelligence'[Management Report] =
    VAR TimeCalc = 'Time Intelligence'[Time Calc]
    RETURN
        INT ( TimeCalc IN { "Current", "Last Year", "Year on Year %" } )

-----------------------------------------------------
-- Calculated Column: 'Time Intelligence'[YTD Report]
-----------------------------------------------------
COLUMN 'Time Intelligence'[YTD Report] =
    VAR TimeCalc = 'Time Intelligence'[Time Calc]
    RETURN
        INT ( CONTAINSSTRING ( TimeCalc, "Year to Date" ) )

These columns are all conditional columns based on Time Calc. It would also be possible to reference other tables when defining these columns. Note, however, that calculation groups are not permitted to have relationships with other tables.

Having added these columns, we can use them to filter or group visuals.

This matrix visual is grouped by Time Calc Category ($ and %).

This page has the filter YTD Report = 1 applied.

Creating an alias column: first attempt

Now let’s suppose that we want to create shorter versions of some of the calculation item names since they are a bit long, especially when included in the above visuals.

For example, let’s shorten “Year to Date” to “YTD” and “Year on Year” to “YOY”.

As a first attempt, here is the definition of a calculated column Time Calc Short where four of the names are shortened and the rest left unchanged:

----------------------------------------------------------
-- Calculated Column: 'Time Intelligence'[Time Calc Short]
----------------------------------------------------------
COLUMN 'Time Intelligence'[Time Calc Short] = 
    VAR TimeCalc = 'Time Intelligence'[Time Calc]
    RETURN
        SWITCH (
            TimeCalc,
            "Current", "Cur",
            "Last Year", "LY",
            "Year on Year", "YOY",
            "Year on Year %", "YOY %",
            TimeCalc
        )

However, if we try to use this column in a visual, the calculation items are not applied as expected. Each column of the matrix visual contains the same values!

Why is this? It turns out that for a calculation item to be applied in a given context, there must be a single value filtered in the original calculation item column. It is not sufficient to cross-filter via another column of the calculation group.

To put it into DAX terms, for a calculation item to be applied in a given context, this expression must be true:

HASONEFILTER ( <Calculation Item Column> )

The problem in the above visual is that each column of the above visual is filtered to a single value of Time Calc Short, but Time Calc (the calculation item column) is unfiltered.

Some undesirable solutions:

  • We could add Time Calc to the visual as well. However, it is not always possible to hide fields in visuals. The matrix column headers above cannot be hidden. So adding Time Calc to the visual would defeat the purpose of having created Time Calc Short!
  • We could create a second calculation group with the shortened calculation item names, but this would mean duplicating the calculation group definition and would be more work to maintain.

Fixing the problem with Group By Columns

It turns out that we can make use of the Group By Columns property discussed in this SQLBI article. Specifically, we can set the Group By Columns property of the alias column (Time Calc Short) to be the original calculation item column (Time Calc). For consistency of ordering of calculation items, we can also set Time Calc Short to sort by Ordinal.

Note that there is an alternative to the Group By Columns method using the Key and Keep Unique Rows properties (also discussed in the SQLBI article), but for simplicity I have chosen to use the Group By Columns method.

Once this Group By Columns property is set, when a DAX query generated by a visual includes the Time Calc Short column, the Time Calc column is automatically added to the query, thereby creating a filter on Time Calc and applying the calculation items as expected.

I will leave it to the reader to examine the DAX queries generated by the visuals 🙂

In summary, the steps to create an alias column for a calculation item column are:

  1. Define an appropriate alias calculated column with DAX.
  2. Use Tabular Editor to set the Group By Columns property of the alias column to the original calculation item column.
  3. Set the Sort By Column property of the alias column to be the original Ordinal column (if desired).

Duplicate values in the alias column

In some cases, we might want to use the same alias for multiple calculation items. For example, we might design our Power BI report so that it is always clear from the context whether we mean “Year on Year” or “Year to Date Year on Year”, and give them both the alias “YOY”.

This is perfectly acceptable with the Group By Columns property set, since the original calculation item column distinguishes the calculation items. To demonstrate, we can create the following column Time Calc Short 2, and set its Group By Columns property in the same way as Time Calc Short:

------------------------------------------------------------
-- Calculated Column: 'Time Intelligence'[Time Calc Short 2]
------------------------------------------------------------
COLUMN 'Time Intelligence'[Time Calc Short 2] = 
    VAR TimeCalc = 'Time Intelligence'[Time Calc]
    RETURN
        SWITCH (
            TimeCalc,
            "Current", "Cur",
            "Last Year", "LY",
            "Year on Year", "YOY",
            "Year on Year %", "YOY %",
            "Year to Date", "YTD",
            "Year to Date Last Year", "YTD LY",
            "Year to Date Year on Year", "YOY",
            "Year to Date Year on Year %", "YOY %",
            TimeCalc
        )

We can then recreate the “YTD Report” page (with filter YTD Report = 1) using Time Calc Short 2 on the visual to confirm that it is working as expected:

Warnings

  • At the time of writing, the Group By Columns property is not accessible in Power BI Desktop, so a tool like Tabular Editor is needed to set this property.
  • The Group By Columns property is only supported in Power BI semantic models, not SQL Server Analysis Services or Azure Analysis Services.
  • Columns with the Group By Columns property set cannot be used within Excel PivotTables connected to a Power BI semantic model using Analyze in Excel (discussed in the SQLBI article).
  • When refreshing a table loaded from Power Query in Power BI Desktop, any Group By Columns properties are cleared for columns in the refreshed table (an apparent bug). This is not a problem for calculation groups or field parameters created using the Power BI interface (which make use of Group By Columns) since these tables are not loaded from Power Query. This problem also does not occur when refreshing semantic models in the Power BI Service. I raised this bug with Microsoft but the response was that any behaviour resulting from the use of external tools is not supported. When the Group By Columns property becomes accessible in Power BI Desktop, I would hope this can be resolved 😉
    UPDATE 30 March 2024: I have tested in the March 2024 version of Power BI Desktop and Group By Columns properties are now retained when refreshing a table loaded from Power Query in Power BI Desktop!

Conclusion

Calculated columns can be added to calculation groups for the purpose of filtering, grouping or providing aliases for the original calculation items.

In order give alias columns the same functionality as the original calculation item column, we must set the Group By Columns property of the alias column to be the original calculation item column. This ensures that the original calculation item column is filtered automatically along with the alias column, thereby applying calculation items correctly.

In future posts, I hope to explore other applications of the Group By Columns property.

2 comments

Comments are closed.