Issue with sorting Months by MonthNumber without duplicates in Concatenate Formula

[jjfr]

Hi All,

I have a Calendar table set up using DAX: 

The table shows Month Names in the 'Month' field, and the relevant numerical value (1-12) in the MonthNumber field.

I've now added a Quick Measure ('Slicer List of Month Values') to concatenate selected month names from a slicer to use in a dymanic page heading. I'm running into an issue when I try to amend the code, to order by MonthNumber instead of Month Name (which is sorting alphabetically). The coding screen does not seem to recognise MonthNumber as a field, and when I include it I get the following error message: "A single value for column 'MonthNumber' in table 'Calendar' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."

I'm new to PowerBI and struggling to figure out what the issue is here. Any advice would be greatly appreciated.

Thanks in advance,

J

Updated to add: I realised I was unable to reference MonthNumber as I was originally referencing the single 'Month' column, rather than the full 'Calendar' table at the start of the Concatenate expression. If I change VALUES('Calendar'[Month]) to VALUES('Calendar') instead, I am able to add a sort by MonthNumber, however that results in duplicate values being displayed (e.g. January, January, January). Are there any suggestions for getting around this? I tried to use DISTINCT but may not be implementing it correctly.

[danextian · Accepted solution]

Hi @jjfr ,

The sort by field must also be included in the virtual table. The virtual table inside TOPN should also be sorted by that field. Try this:

List of Month Short values =
VAR __DISTINCT_VALUES_COUNT =
    DISTINCTCOUNT ( 'Dates'[Month Short] )
VAR __MAX_VALUES_TO_SHOW = 3
RETURN
    IF (
        __DISTINCT_VALUES_COUNT > __MAX_VALUES_TO_SHOW,
        CONCATENATE (
            CONCATENATEX (
                TOPN (
                    __MAX_VALUES_TO_SHOW,
                    SUMMARIZE ( Dates, 'Dates'[Month Short], Dates[Month Number] ),
                    [Month Number], ASC
                ),
                [Month Short],
                ", ",
                [Month Number], ASC
            ),
            ", etc."
        ),
        CONCATENATEX (
            SUMMARIZE ( Dates, 'Dates'[Month Short], Dates[Month Number] ),
            [Month Short],
            ", ",
            [Month Number], ASC
        )
    )

[jjfr]

That worked perfectly @danextian! Thanks so much for your help, can't tell you how much it's appreciated.

[jjfr]

Thanks very much for your suggestion @AnalyticPulse. I'm not actually having an issue with visalisations though. At the moment I've been able to sort them all by MonthNumber while displaying the Month Name. 

I only seem to be having this issue with the concatenate formula. But I need it this formula to work to generate the list of Months selected for my report as a string. Do you have any suggestions on why my concatenate formula might not be working? 

[AnalyticPulse]

Hi @jjfr 

if you just want to sort the month to show on visual you can follow different approach, add new column for sorting with combination of year and month and sort your column based on this new yearmonth order column. Then in report visual sort that visual data based on the month asc by clicking three dots of the visual. 

Learn Power BI free:

https://analyticpulse.blogspot.com

Learn Power BI free

DAX Functions

Powerbi Visualisation