Forum Discussion

jjfr's avatar
jjfr
Frequent Visitor
2 years ago
Solved

Issue with sorting Months by MonthNumber without duplicates in Concatenate Formula

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.

 

Need Help
  • danextian's avatar
    danextian
    2 years ago

    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
        )
    )

     

     

4 Replies

  • danextian's avatar
    danextian
    Icon for Super User rankSuper User
    2 years ago

    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's avatar
      jjfr
      Frequent Visitor
      2 years ago

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

  • jjfr's avatar
    jjfr
    Frequent Visitor
    2 years ago

    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?