data table : Fetch me the latest date row based on dimension column

[Dsharma43]

Hello i am trying to create a table in data view, below is the sample data

metric_date	metric_id	metric_results_status	Level I Risk
10/31/2020 0:00	1799320	Amber	IS
9/30/2020 0:00	1799320	RED	IS
8/31/2020 0:00	1799320	Amber	IS
7/31/2020 0:00	1799320	Amber	IS
9/30/2020 0:00	1799321	Green	AS
8/31/2020 0:00	1799321	Amber	AS

i need to get the metricID,results_status,metric_date and level1risk column such that it has to find the latest metric date and fetch me that row.
for an exmple for the metricID : 1799320 the latest date is 31st october, so fetch me only that row and similarly for metricID : 1799321 fetch me sept row as it is the latest date for that ID.

i tried using summarize, calculatetable and filter but i am not able to get desired results.
Could someone please help me with this. Thanks in advance.

Regards,

D

[Tahreem24]

@Dsharma43 ,

Simple way to do it by selecting "Latest" option on Metrics_Date, Metrics_Result_Status, and Level I Risk columns on the table visual likethe below screen shot:

[Dsharma43]

Hi @Tahreem24 ,

Thanks for the response.

I am creating a table in data view pane. could you please let me know how can i achieve that.

Regards,

D

[Tahreem24 · Accepted solution]

@Dsharma43 ,

Create new Table with below DAX:

New Table = SUMMARIZE(TABLE1,TABLE1[metric_id],"Date",MAX(TABLE1[metric_date]),"Metric_Status",MAX(TABLE1[metric_results_status]),"Level Risk",MAX(TABLE1[Level I Risk]))

[Dsharma43]

Thanks @Tahreem24 for the quick response.

Regards,

D