cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Dsharma43
Helper II
Helper II

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

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

 

metric_datemetric_idmetric_results_statusLevel I Risk
10/31/2020 0:001799320AmberIS
9/30/2020 0:001799320REDIS
8/31/2020 0:001799320AmberIS
7/31/2020 0:001799320AmberIS
9/30/2020 0:001799321GreenAS
8/31/2020 0:001799321AmberAS

 

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

 

1 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]))
 
Capture.PNG
Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, and Sales Analysis Report

View solution in original post

4 REPLIES 4
Tahreem24
Super User II
Super User II

@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:

Capture.PNG

Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, and Sales Analysis Report

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

 

@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]))
 
Capture.PNG
Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, and Sales Analysis Report

View solution in original post

Thanks @Tahreem24 for the quick response.

 

Regards,

D

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors