Forum Discussion

Sudharshan1919's avatar
Sudharshan1919
Helper III
4 years ago

Getting Distinct data by Latest Date

Hi,

 

I have the below data, my requirement is like getting the latest data by date and should get distinct records. The excel file will be updated daily 3-4 times and so the data will be duplicated (means if for Region 1 the data is inserted at 03:00 AM with some servers and again the data will be inserted at 09:00 AM with the same set of servers with their respective status).

I am appending the files into a new table. (means for Region1 there is seperate file and seperate path and similarly for other regions)

 

 As you can see on the above image for Region 1 - Latest data updated at 6/14/2022 04:00 PM ( there are 2 servers and 2 servers must be counted) and for Region 2 - Latest data updated at 6/14/2022 09:00 AM, if you observe  for region 2 there is a data for 03:00 AM too, so i dont need that record to be considered and it should consider only the Latest Updated Date/Time and show as a Uniquie. Similarly as in the Region 3.

Can we achieve this through measures/ calculated columns. Kindly suggest on how can we achieve this.

NOTE : My approach of Visualization is showing the count in "Cards" and the Conditions on applying the data is to get "Registered/Unregistered" Data

Below should be my data after all the filters/measures are used.

 

Thanks

General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests

1 Reply

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Sudharshan1919 , use measure for all columns other than region and DateTime, for DateTime tale max

     

    Measure =
    VAR __id = MAX ('Table'[Region] )
    VAR __date = CALCULATE ( MAX('Table'[Datetimefetched] ), ALLSELECTED ('Table' ), 'Table'[Region] = __id )
    return
    CALCULATE ( max ('Table'[servername] ), VALUES ('Table'[Region] ),'Table'[Region] = __id,'Table'[Datetimefetched] = __date )