locked
Sorting the results of DAX expression RRS feed

  • Question

  • I have a PBIX report that contains a list of dates. I need to sort the dates chronologically. Below is the DAX expression that I need to change. Below that is the current result with the dates out of order. Does anyone have a method to sort the dates?

    TitleDate = if(DISTINCTCOUNT('CEx Dates'[YearMonthName]) = [Distinct Date Count],  "Note: Data for " & "All Months", "Note: Data for " & CONCATENATEX( VALUES('CEx Dates'[YearMonthName]), 'CEx Dates'[YearMonthName], ", "))

    Wednesday, July 18, 2018 3:25 PM

Answers

  • Hi Yuan, Thanks for your suggestion. I'm having a bit of trouble understanding how to fit your syntax into the SQL I included. Luckily I was able to resolve my issue by adding an order by to the SQL in the query. Thanks for your help! Mark

      Select distinct depart_mo, b.YearMonthName
      from sip.MP_Segment_Data A
      inner join dbo.Dim_Date B
      on b.YearMonth = a.Depart_Mo
    order by depart_mo

    • Marked as answer by mark156531 Friday, July 20, 2018 8:26 PM
    Friday, July 20, 2018 8:26 PM

All replies

  • I think you could use TOPN to force the results to be sorted and then concatenate over that. The following sorts by CalendarMthKey and then returns the [Calendar Mth] (which contains the month name)

    eg.

    CONCATENATEX (
            SELECTCOLUMNS (
                TOPN (
                    9999,
                    SUMMARIZECOLUMNS ( 'date'[Calendar Mth], 'date'[CalendarMthKey] ),
                    'date'[CalendarMthKey], ASC
                ),
                "Calendar Mth", 'date'[Calendar Mth]
            ),
            [Calendar Mth],
            ","
        )


    http://darren.gosbell.com - please mark correct answers

    Thursday, July 19, 2018 12:20 AM
  • Hi mark156531,

    Thanks for your question.

    To solve your question more efficiently, would you mind typing out 5-10 rows of example data for these table, then showing what results you are expecting based on those sample data? It is much better if you can share the PBIX file. Do mask sensitive data before uploading.

    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Thursday, July 19, 2018 3:00 AM
  • Hi Yuan, Thanks for your suggestion. I'm having a bit of trouble understanding how to fit your syntax into the SQL I included. Luckily I was able to resolve my issue by adding an order by to the SQL in the query. Thanks for your help! Mark

      Select distinct depart_mo, b.YearMonthName
      from sip.MP_Segment_Data A
      inner join dbo.Dim_Date B
      on b.YearMonth = a.Depart_Mo
    order by depart_mo

    • Marked as answer by mark156531 Friday, July 20, 2018 8:26 PM
    Friday, July 20, 2018 8:26 PM
  • Hi Yuan, Thanks for your suggestion. I'm having a bit of trouble understanding how to fit your syntax into the SQL I included. Luckily I was able to resolve my issue by adding an order by to the SQL in the query. Thanks for your help! Mark

      Select distinct depart_mo, b.YearMonthName
      from sip.MP_Segment_Data A
      inner join dbo.Dim_Date B
      on b.YearMonth = a.Depart_Mo
    order by depart_mo

    There are a couple of things to be aware of with this solution because what you have done is to just influence the order that the data is stored in the underlying column.

    The potential issues with this approach are:

    1. One of the compression optimization steps the tabular engine does is to test different sort orders, so as you add more data to your date dimension this may stop working
    2. You can't use this technique to sort multiple columns (eg. MonthName and DayOfWeekName)
    3. Certain query patterns may apply other sort criteria and cause your measure to go out of order

    So while it works at the moment you will need to keep your eye on this as it not always work


    http://darren.gosbell.com - please mark correct answers

    Monday, July 23, 2018 5:27 AM