Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Dynamic text with month not sorting (screenshot)

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 

Need Help

14 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    7 years ago

    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.

  • NicoleTh823's avatar
    NicoleTh823
    Regular Visitor
    7 years ago

    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
  • bajimmy1983's avatar
    bajimmy1983
    Icon for Advocate V rankAdvocate V
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      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's avatar
        bajimmy1983
        Icon for Advocate V rankAdvocate V
        7 years ago

        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