Forum Discussion

ColinA's avatar
ColinA
New Member
4 years ago
Solved

Sort within Combined Date Field

I am building a visual for a user that wants to see the last 5 times an update has been submitted.  So I have an ID field that will have multiple dates associated with each ID.  I can get the dates combined into a single cell, but they are out of order.

Here is a view of the data:

The Formula I have in the CombineDates field is currently:

CombineDates = CALCULATE(CONCATENATEX('Table','Table'[Date]," "),ALLEXCEPT('Table','Table'[ID]))
Is there a better way to return this so that I get the last 5 dates present for each ID?
Need Help
  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    4 years ago

    Hi ColinA ,

     

    First, we figurt out that we need CONCATENATEX() function to combine all the date, so the problem is how to get the last 5 date. TopN() to get the result.

    So DAX expression like the following:

     

    expected result =
VAR _top =
    TOPN ( 5, FILTER ( 'table', [ID] = EARLIER ( 'table'[ID] ) ), [date], DESC )
RETURN
    CONCATENATEX ( _top, [date], " " )

     

    Modify this measure to suit your model.

     

    Best Regards

    Community Support Team _ chenwu zhu

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

    • ColinA's avatar
      ColinA
      New Member
      4 years ago

      Sorry, posted this in the wrong location.  Ideally the new column would retain only the latest 5 dates per ID, and would order them before concatenating them with a delimiter.

       

      • v-chenwuz-msft's avatar
        v-chenwuz-msft
        Community Support
        4 years ago

        Hi ColinA ,

         

        First, we figurt out that we need CONCATENATEX() function to combine all the date, so the problem is how to get the last 5 date. TopN() to get the result.

        So DAX expression like the following:

         

        expected result =
VAR _top =
    TOPN ( 5, FILTER ( 'table', [ID] = EARLIER ( 'table'[ID] ) ), [date], DESC )
RETURN
    CONCATENATEX ( _top, [date], " " )

         

        Modify this measure to suit your model.

         

        Best Regards

        Community Support Team _ chenwu zhu

         

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • ColinA's avatar
    ColinA
    New Member
    4 years ago

    Ideally the new column would retain only the latest 5 dates per ID, and would order them before concatenating them with a delimiter.