Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Transform data only most recent

Hi all, how do I use Query Editor to filter out the records with the old date? For example, with Ticket ID 12 and 14 I only want to keep the record with the most recent date (9/21/20 for Ticket 12, 9...
Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    5 years ago

    Hi Anonymous ,

     

    Create a measure as below:

    Day = 
var _latestday=CALCULATE(MAX('Table'[Date]),FILTER(ALL('Table'),'Table'[Ticket ID]=MAX('Table'[Ticket ID])))
Return
IF(MAX('Table'[Date])=_latestday,MAX('Table'[Date]),BLANK())

    Or a calculated column as below:

    Column = 
var _latestday=CALCULATE(MAX('Table'[Date]),FILTER('Table','Table'[Ticket ID]=EARLIER('Table'[Ticket ID])))
Return
IF('Table'[Date]=_latestday,'Table'[Date],BLANK())

    And you will see:

    For the related .pbix file,pls see attached.

     

    Best Regards,
    Kelly

    Did I answer your question? Mark my post as a solution!

v-kelly-msft's avatar
v-kelly-msft
Community Support
5 years ago

Hi Anonymous ,

 

Create a measure as below:

Day = 
var _latestday=CALCULATE(MAX('Table'[Date]),FILTER(ALL('Table'),'Table'[Ticket ID]=MAX('Table'[Ticket ID])))
Return
IF(MAX('Table'[Date])=_latestday,MAX('Table'[Date]),BLANK())

Or a calculated column as below:

Column = 
var _latestday=CALCULATE(MAX('Table'[Date]),FILTER('Table','Table'[Ticket ID]=EARLIER('Table'[Ticket ID])))
Return
IF('Table'[Date]=_latestday,'Table'[Date],BLANK())

And you will see:

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!