Forum Discussion

TechR21's avatar
TechR21
Icon for Helper V rankHelper V
3 years ago

Selecting most recent record in table based on date

Hi,

 

Im creating a report based on a "table" visual (not a matrix) . This visual has following columns (+ 10 others that are not important for this case). The report is basically a big dump of specific data that will be exported later. Below an example

 

 

Now when there are 2 "duplicate" id's, it means that there has been a change somewhere in a column that is not part of this visual. These changes are tracked by the modifcation date.

 

I use the measure:

Last date = var _max = maxx(filter(ALLSELECTED('Table (2)'), 'Table (2)'[id] = Max('Table (2)'[id])), 'Table (2)'[moddate])
return
CALCULATE(max('Table (2)'[id]), filter(('Table (2)') ,  'Table (2)'[moddate] =_max))

 

so result should be as below: But the "Last date column" should not be visible. This is created by a measure that i use on the table

So I need to select a ticket id based on the most recent modification date. This way every ticket id is unique in the visual. Whats the easiest way to do this, without showing the measure as a column in the table?

 

No option to add a calculated column, I want only to have the columns visible that are supposed to be visual in the table. this for export reasons

original data for this table will be refreshed from a odatafeed, and contains a lot more records then these 4 obviously 😉

Need Help

12 Replies

    • TechR21's avatar
      TechR21
      Icon for Helper V rankHelper V
      3 years ago

      I see in your blog, you make use of 3 columns? i tried following measure but when i apply to my table visual the whole visual fails to give back any data

       

      Latest MDate = var _max = maxx(filter(ALLSELECTED(Incidents), Incidents[id] = Max(Incidents[id])), Incidents[modificationDate])
      return
      CALCULATE(max(Incidents[id]), filter((incidents) ,  Incidents[modificationDate] =_max))
       
      Also i dont use the modification column in my visual, but its in the table. I see i put it wrong in my initial question
  • bhl's avatar
    bhl
    Regular Visitor
    3 years ago
    hi Tech R21
     
    mabye somthing like this
    create last day meassure 
    m_date = (LASTDATE('Sheet'[modif_date]))
     
    last_date_measure =
    VAR maxd =
    CALCULATE (
    MAX ( Sheet[modif_date]),
    ALLSELECTED (  Sheet[id] ),
    VALUES ( Sheet[id])
    )
    RETURN
    IF ( MAX ( Sheet[modif_date] ) = maxd, 1, BLANK () )
     

     

    • TechR21's avatar
      TechR21
      Icon for Helper V rankHelper V
      3 years ago

      i dont use a matrix, but table visual. Also no extra column in the visual is allowed, due to exports of the report. Updated my initial question for clarity

    • TechR21's avatar
      TechR21
      Icon for Helper V rankHelper V
      3 years ago

      i dont use a matrix, but table visual. Also no extra column in the visual is allowed, due to exports of the report. Updated my initial question for clarity

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi TechR21 ,

    You can try below: 

    ValidEntry =
    Var _ticketID = SELECTEDVALUE(tickets[Ticket ID])

    Var _table = FILTER(
                    ADDCOLUMNS(DISTINCT(SELECTCOLUMNS(tickets,"ID",[Ticket ID], "MOD", tickets[Modification Date])), "Rank", CALCULATE(
                    COUNTROWS('tickets'),
                    FILTER(
                        ALL(tickets),
                        'tickets'[Ticket ID] = EARLIER([ID]) &&
                        'tickets'[Modification Date] > EARLIER([MOD])
                        )
                        )+1),
                        [Rank] =1)
    RETURN
    TOPN(1,SELECTCOLUMNS( FILTER(_table, [Rank] =1 && [ID] = _ticketID), "x",[Rank]))


    -----------------------------
    This will always show you valid entries marked as 1. You can then apply a visual level filter on the 'ValidEntry' measure returning non-blank

     

    Please mark it as answer If you find it useful. 
    Thanks! 

    • TechR21's avatar
      TechR21
      Icon for Helper V rankHelper V
      3 years ago

      this works ! i made a mistake when applying this but got it working, thanks! question; will this work when columns are in two different tables?

       

      so for example ticketid is in table tickets, and modificationdate is in table ticketdetails

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        Yes, it will, the two tables will have to be related for that to work.
        and measure will be slightly modified. New measure will look like: 

        ValidEntryNew =
        Var _ticketID = SELECTEDVALUE('tickets main'[Ticket ID])

        Var _table = FILTER(
                        ADDCOLUMNS(DISTINCT(SELECTCOLUMNS('tickets details',"ID",[Ticket ID], "MOD", 'tickets details'[Modification Date])), "Rank", CALCULATE(
                        COUNTROWS('tickets details'),
                        FILTER(
                            ALL('tickets details'),
                            'tickets details'[Ticket ID] = EARLIER([ID]) &&
                            'tickets details'[Modification Date] > EARLIER([MOD])
                            )
                            )+1),
                            [Rank] =1)
        RETURN
        TOPN(1,SELECTCOLUMNS( FILTER(_table, [Rank] =1 && [ID] = _ticketID), "x",[Rank]))

         



  • TKCillie's avatar
    TKCillie
    Regular Visitor
    1 year ago

    Hello, I am trying to find the most recent consent change by datetime, but also by channel, I am struggling with the subchannel seperation:

     

    This is what I used, it returns the last entry YES but not by Subchannel:

    Most Recent Entry =
    Var _consentID = SELECTEDVALUE(Consent[Account ID 18 Char])

    Var _table = FILTER(
                    ADDCOLUMNS(DISTINCT(SELECTCOLUMNS(Consent,"ID",[Account ID 18 Char], "MOD", Consent[Capture Datetime].[Date])), "Rank", CALCULATE(
                    COUNTROWS('Consent'),
                    FILTER(
                        ALL(Consent),
                        'Consent'[Account ID 18 Char] = EARLIER([ID]) &&
                        'Consent'[Capture Datetime].[Date] > EARLIER([MOD])
                        )
                        )+1),
                        [Rank] =1)
    RETURN
    TOPN(1,SELECTCOLUMNS( FILTER(_table, [Rank] =1 && [ID] = _consentID), "x",[Rank]))