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

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

MBAS Carousel

Sign up for our May 4th event!

May the fourth be with you, join us online!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.