Background

In the SQLBI article Understanding Group By Columns in Power BI, Marco Russo & Alberto Ferrari show how the Group By Columns property allows the values in one column to be “grouped by” unique key values defined by one or more other columns, rather than by the original column’s unique values.

For example, setting the Group By Columns property for a City column can allow multiple cities named “Portland” (say, “Portland, Oregon” and “Portland, Ontario”) to behave as distinct values within Power BI visuals, as long as suitable columns exist in the same table to define a key that uniquely identifies each City. The key can be a single column such as City ID, or a composite key such as City Name, State, Country.

The problem of weekday/month initial labels

Just as multiple cities share the name “Portland”, multiple weekdays share the same initial letter. In English, for example, “T” is the initial of Tuesday and Thursday and “S” is the initial of Saturday and Sunday:

M, T, W, T, F, S, S

For English months, the initials look like this:

J, F, M, A, M, J, J, A, S, O, N, D

If we include Weekday Initial and Month Initial columns in our Date table with no additional configuration, visuals will end up looking similar to those shown below. The weekday and month initials appear nonsensical since they are aggregated based on the initial letter (rather than the underlying value) and sorted alphabetically (F, M, S, T, W and A, D, F, J, M, N, O, S):

This problem of ambiguous initials for weekdays and months is of course a feature of many other languages as well!

Potential solutions that don’t work

You might wonder whether this problem can be solved with suitable format strings, but unfortunately it cannot (to my knowledge at least — let me know if you have a working method! 🙂). There are no format strings that can be specified for columns that return the initial letters of months or weekdays. The shortest abbreviation that format strings can produce for weekdays or months (for columns of type date) is 3 letters, such as “Jan” (using "mmm") or “Mon” (using "ddd").

Another tempting solution is to just specify a Sort By Column. For example, you could set Weekday Initial‘s Sort By Column to Weekday Number, and Month Initial‘s Sort By Column to Month. This is not possible with Power BI Desktop (it throws an error), but it can be forced with Tabular Editor. This works to an extent, but it causes odd behaviour when the columns are used in slicers. For example, with Weekday Initial, when selecting the second “T” or “S” after selecting some other value, the selection jumps back to the first occurrence of that value:

Solution 1: Add invisible characters

One solution to this problem is described in Daniil Maslyuk’s excellent post Single-letter day and month names in Power BI and in this solution on the Fabric Community Forums from 2018. The solution is to modify the initial letters by adding invisible characters (such as the zero-width space) to enforce uniqueness while leaving the appearance unchanged.

Using English weekdays as an example, Weekday Initial could be set up as shown below. A suitable Sort By Column would also be needed.

Solution 2: Group By Columns

The Group By Columns property provides a convenient alternative solution:

1. Ensure that the Date table includes appropriate key columns for weekdays and months, such as Weekday Number and Month Number.
2. For the Weekday Initial column, set the Group By Columns property to Weekday Number.
3. For the Month Initial column, set the Group By Columns property to Month.

Here is an example of how I would suggest setting up these columns. For convenience, Month and Weekday Number can also be used as Sort By Columns.

At the time of writing, the Group By Columns property cannot be set in Power BI Desktop, so an external tool such as Tabular Editor is needed.

Sample Power BI model using Group By Columns

The sample PBIX file attached to this post includes daily values of two stock market indices from January 1920 to October 2024:

• Dow Jones Industrial Average (query at stooq.com)
• Dow Jones Transportation Average (query at stooq.com).

The model’s Date table includes these columns with Group By Columns configured:

The reason for setting Group By Columns for Year Label Short and Week Label Short English is that this dataset spans more than a century, so two-digit representations of years are ambiguous. In the case of Year Label Short, the label “CY 20” could represent either 1920 or 2020, but setting Group By Columns to Year solves this problem.

An example of how Month Initial English is configured in Tabular Editor is shown below:

Sample Power BI visuals

With the Group By Columns properties configured as shown above, here are some visuals that show Group By Columns in action (note that these indices are assigned values only on weekdays):

Considerations

The SQLBI article Understanding Group By Columns in Power BI provides a great discussion of the side effects of the Group By Columns property. These are the main considerations I consider important:

1. At the time of writing, the Group By Columns property is available only for Power BI semantic models, not SQL Server Analysis Services or Azure Analysis Services.
2. Columns with the Group By Columns property set cannot generally be included as grouping fields in Excel PivotTables or DAX queries generated by Excel or other external tools, unless all columns from the Group By Columns collection are also included.
3. When a column with the Group By Columns property set is used as a grouping field in a Power BI visual (e.g. row, column or axis field), all columns in the Group By Columns collection (and the Sort By Column if any) are included in the DAX query generated by the visual. This must be taken into account when modifying filters. This is a similar side effect to that of Sort By Columns as discussed in the SQLBI article Side effects of the Sort By Column setting in DAX.
4. Filters on columns with the Group By Columns property set are translated by Power BI into filters on the columns in the Group By Columns collection. I will cover one application of this in my next post.
5. The Group By Columns property should be applied sparingly to columns, since it limits the use of those columns in Excel and non-Power BI client tools, as well as complicating DAX expressions involving those columns.

To illustrate point 3, here is the __DS0Core variable within the DAX query for the visual Daily Close (English Weekday Initials) above. The highlighted lines are added to the query due to Group By Columns defined in the model: Week Start (for Week Label Short English) and Weekday Number (for Weekday Initial English):

VAR __DS0Core =
	SUMMARIZECOLUMNS (
        -- Group By / Sort By Columns
		'Date'[Week Start],
		'Date'[Weekday Number],
        -- Columns actually included in the visual
		'Date'[Week Label Short English],
		'Date'[Weekday Initial English],
		'Index'[Index Name],
		__DS0FilterTable,
		__DS0FilterTable2,
		"Close_Average_Latest", 'Key Measures'[Close Average Latest]
	)

To illustrate point 4, here are the initial variables within the DAX query for the visual Daily Close (English Weekday Initial Slicer) above. The highlighted line shows that the selection Weekday Initial English = “F” is translated into the filter Weekday Number = 4.

DEFINE
VAR __DS0FilterTable = 
	TREATAS( { 2024 }, 'Date'[Year] )

VAR __DS0FilterTable2 = 
    -- Weekday Initial English = "F" is translated into Weekday Number filter:
	TREATAS( { 4 }, 'Date'[Weekday Number] ) 

VAR __DS0Core = 
	SUMMARIZECOLUMNS (
		'Date'[Date],
		'Index'[Index Name],
		__DS0FilterTable,
		__DS0FilterTable2,
		"Close_Average_Latest", 'Key Measures'[Close Average Latest]
	)

Sample PBIX

Please download the PBIX file and data files used in this post here. Change the Power Query parameter CSV Folder to the local folder where you have saved the CSV files.

What do you think?

I look forward to hearing your thoughts here or on LinkedIn!