Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
TechR21
Helper V
Helper V

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

 

TechR21_0-1668698773537.png

 

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

TechR21_1-1668698857976.png

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 😉

11 REPLIES 11
Rushki
Regular Visitor

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
Rushki_0-1668694050799.png

 

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

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

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]))

Rushki_0-1668751684213.png

 



The only "problem" I see is the linking of the tables, because i need the ticketnumber in my visual, I put for some reason the ticketid in the visual.

 

The table "tickets" has ticketnumber, and an id.

The table "ticketdetails" has modificationdate

 

the tables ("tickets"and "ticketdetails") are linked by id(in tickets) and ticketid (in ticketdetails)

 

So to use your query i need to make the link based on link between the two tables but the calculation needs to be done on the ticketnumber, but this I cant seem to make it work in your last query

If your relationship is based on ticket ID then also it will work. 
since Ticket Details table will be your child to tickets main table, with a many-to-one relationship (ticket details (many) to tickets main (one)), you can add a DAX column into ticket details fetching RELATED(TicketNumber) and in above query replace ticketid by ticket number, 
I believe that will work

If you want it by unique ticket ID you use ticket Id, If you want it by unique ticket number, you use ticket number. 

Still you can put as many columns as you want on the table visual and paint them in white so as to make them hidden and set the width to smallest.

yes the relationship is already there, via the Model. What I try to do is include this in your last query, because the previous one was working, and i can easily apply this measure as a filter. But Im not sure how to put the link between the two tables in the formula

bhl
Regular Visitor

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 () )
 
bhl_0-1668692209281.png

 

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

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

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.