Dynamic text with month not sorting (screenshot)

[Anonymous]

Hi team, 

I have a date field from which i am extracting month : 

Month Name = SWITCH(FSP[Month],1,"January",2,"February",3,"March",4,"April",5,"May",6,"June",7,"July",8,"August",9,"September",10,"October",11,"November",12,"December")

Now i have created a measure "title month " (formula below) and based on the slicer selection it shows the month selected. 

Title Month = " Month: " & CONCATENATEX(   VALUES( FSP[Month Name]), FSP[Month Name], ", ")

This is working fine. but its not sorting as per the month. it gives wierd header (not sorted according to selection slicer; but random) . I want it sorted 

e.g. if march, june, january is selected     Show as "Month: January, march June

and if selected all Show as "Month: All" 

P.s: in the dataset month name is sorted based on month 

Please help me. If have attached screenshots 

[bajimmy1983]

Hi all! 

It seems we don't have a solution yet for this issue using CONCATENATEX... I am facing the same problem rightnow I don't know how to fix it! Had anyone discovered how to do so? Please share 🙂

Take a look at the picture I am attaching.

Month Name sorted incorrectly

[Anonymous]

Hi @bajimmy1983 

use the following steps :

1. Add a new column that represents the intended sort order (ex: Jan = 1, Feb =2). This can be a calculated column using the MONTH() or FORMAT() function to apply to a date column or manually created. You can also use this if your organization uses a different fiscal year than the standard calendar year.
2. On the fields pane, select the Month column, navigate to the Modeling tab, click "Sort by Column", and update the sort to select the SortNumber column created in step #1. Tip: Make sure your new column is an integer/whole number, so the sorting is numeric.
3. Add your visuals to see the sort order reflected as intended.

Let me know if this works 

Thanks

Ronak 

[bajimmy1983]

Hi @Anonymous ! First thanks a lot to reply. 

Please see in this new photo I already have "Sort by Column" configured. All my charts are ok, too (Jan, Feb, Mar, Apr and so on), but just using CONCATENATEX function this "sort by" lost itself. 

Measure I'm using: 

I colored by blue in the "Month" part.

I want to use the same approach (TOPN function) Months as I used for Years, but when I apply TOPN function like below I got that first error I shared in the first photo (months are not sorted correctly).

TOPN ( __MAX_VALUES_TO_SHOW_MONTH; VALUES ( 'Date'[MONTH] ); 'Date'[MONTH]; ASC ); 'Date'[MONTH]; ", "; 'Date'[MONTH]; ASC )

List of Year-Months values = 
VAR __DISTINCT_VALUES_COUNT_YEAR =
    DISTINCTCOUNT ( 'Date'[Year] )
VAR __DISTINCT_VALUES_COUNT_MONTH =
    DISTINCTCOUNT ( 'Date'[MONTH] )
VAR __MAX_VALUES_TO_SHOW_YEAR = 6
VAR __MAX_VALUES_TO_SHOW_MONTH = 6
RETURN
    CONCATENATE (
        IF (
            ISFILTERED ( 'Date'[Year] );
            IF (
                __DISTINCT_VALUES_COUNT_YEAR > __MAX_VALUES_TO_SHOW_YEAR;
                CONCATENATE (
                    "Dados de: ";
                    CONCATENATE (
                        CONCATENATEX (
                            TOPN ( __MAX_VALUES_TO_SHOW_YEAR; VALUES ( 'Date'[Year] ); 'Date'[Year]; ASC );
                            'Date'[Year];
                            ", ";
                            'Date'[Year]; ASC
                        );
                        ", etc. "
                    )
                );
                CONCATENATE (
                    "Dados de: ";
                    CONCATENATEX ( VALUES ( 'Date'[Year] ); 'Date'[Year]; ", "; 'Date'[Year]; ASC )
                )
            );
            "Dados de todos os Anos "
        );
        IF (
            ISFILTERED ( 'Date'[MONTH] );
            IF (
                __DISTINCT_VALUES_COUNT_MONTH > __MAX_VALUES_TO_SHOW_MONTH;
                " e vários Meses";
                CONCATENATE (
                    " e Meses: ";
                    CONCATENATEX (
                        VALUES ( 'Date'[MONTH] );
                        'Date'[MONTH];
                        ", ";
                        'Date'[MONTH]; ASC
                    )
                )
            );
            " e todos Meses"
        )
    )

Photo: 

Sort by Column option is OK 

[NicoleTh823]

I created Dates table

Dates =

GENERATE (

CALENDAR( DATE( YEAR( NOW() ) - 1, MONTH( NOW() ), DAY( NOW()) ), NOW()),

VAR currentDay = [Date]

VAR day = DAY( currentDay )

VAR month = MONTH ( currentDay )

VAR monthName = FORMAT ( [Date], "mmmm" )

VAR year = YEAR ( currentDay )

RETURN ROW (

"day", day,

"month", month,

"monthName", monthName,

"year", year )

)

Add 'Dates'[Year] and 'Date'[monthName] slicers & sort MonthName slicer by 'Dates' [month]

 Then, create a new measure for the title in the Date table

[Greg_Deckler]

So, the order returned is the order encountered within the data. What I would suggest is that you sort your data on import in Power Query based upon the Month column. Then, you order will be correct in your DAX.

[Anonymous]

No this is not working. Also because  I am not importing ; I am using a direct query with joins 

[v-yuta-msft]

Hi ronakvi14,

CONCATENATEX can set sorting option like pattern below:

CONCATENATEX (
   <table>, <expression>, [<delimiter>], [<OrderBy_Expression1>], [<Order>] …
) 

Please refer to: https://www.tutorialspoint.com/dax_functions/dax_concatenatex_function.htm .

Regards,

Jimmy Tao

[Anonymous]

Hi ,

I tried using this expression 

Title Month = " Month: " & CONCATENATEX(   FSP, FSP[Month Name], ", ", FSP[Month],asc)

but its giving the error 

Error Message:
MdxScript(Model) (9, 27) Calculation error in measure 'FSP'[Title Month]: Function 'PLACEHOLDER' encountered a Text that exceeds the maximum allowable length.

fsp[month] has numeric data (1,2,3 correcsponding to the month)

fsp[month name] has jan,feb, ,march 

need to sort by month in dynamic header rather than the month name.. but display the months selected by the user (its a slicer) 

[alena2k]

I think that you still need to use VALUES to get your unique list e.g.

Title = "Months: " & IF(COUNTROWS(VALUES(Months)) = 12, "All" ,CONCATENATEX(VALUES(Months), Months[Name], ", ", Months[Num]))

[Anonymous]

If i write this : 

Title = "Months: " & IF(COUNTROWS(VALUES(FSP[Month])) = 12, "All" ,CONCATENATEX(VALUES(FSP[Month]), FSP[Month], ", ", FSP[Month]))

this is just giving me month numbers sorted.. 

but i want month name sorted by month numbers 

[Anonymous]

If I try this: 

Title m = "Months: " & IF(COUNTROWS(VALUES(FSP[Month])) = 12, "All" ,CONCATENATEX(VALUES(FSP[Month Name]), FSP[Month Name], ", ", FSP[Month]))

it gives me the error: a table of multiple values was supplied where a single value was expected

[alena2k]

Try MIN for the order and I think that DISTINCT is better then VALUES

Title m = "Months: " & IF(COUNTROWS(DISTINCT(FSP[Month])) = 12, "All" ,CONCATENATEX(DISTINCT(FSP[Month Name]), FSP[Month Name], ", ", MIN(FSP[Month])))

[Anonymous]

Its not sorting. maybe thats a limitation. 

Not sure. Have been looking into blogs and videos in order to resolve this. 

[alena2k]

Very strange. Can you isolate dataset and share with me? It is so transparent that it must work.