Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
Anonymous
Not applicable

Measure to get the maximum submission date for a single unique ID?

I have an excel file in my Power BI workbook that has repeat values for the primary ID, Org Code. It's a unique ID assigned to each company, shown below

 

 

jsauerla_0-1614009821159.png

 

 

My objective is to create a measure which calculates the latest (maximum) submission date for a company when the Process Step is Approval, Upload, or Resubmission. Shown below is the excel version of this calculation, and what I'd like to have in Power BI

 

example2.PNG

 

Here's the code I have tried -- believe I might have an erorr somewhere as it doesn't work... I want the MAX of the three upload approval resubmission for each unique org code

 

Latest Submission Date = CALCULATE(MAX('Approval Process Audit Log'[Status Change Date]), FILTER('Approval Process Audit Log','Approval Process Audit Log'[Process Step] = "Upload" , 'Approval Process Audit Log'[Process Step] = "Approval" , 'Approval Process Audit Log'[Process Step] = "Resubmission"))

 

 

1 ACCEPTED SOLUTION
v-jayw-msft
Community Support
Community Support

Hi @Anonymous ,

 

Check the measure.

Measure = CALCULATE(MAX('Table'[date]),FILTER(ALLEXCEPT('Table','Table'[id]),'Table'[step]<>"rejection"))

15.PNG 

 

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

View solution in original post

4 REPLIES 4
v-jayw-msft
Community Support
Community Support

Hi @Anonymous ,

 

Check the measure.

Measure = CALCULATE(MAX('Table'[date]),FILTER(ALLEXCEPT('Table','Table'[id]),'Table'[step]<>"rejection"))

15.PNG 

 

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
amitchandak
Super User
Super User

@Anonymous , Try like

 

Latest Submission Date = CALCULATE(MAX('Approval Process Audit Log'[Status Change Date]), FILTER('Approval Process Audit Log','Approval Process Audit Log'[Process Step] in{ "Upload" , "Approval" , , "Resubmission"}))

 

Latest Submission Date = CALCULATE(MAX('Approval Process Audit Log'[Status Change Date]), FILTER('Approval Process Audit Log','Approval Process Audit Log'[Process Step] in{ "Upload" , "Approval" , , "Resubmission"}) , allexcept('Approval Process Audit Log','Approval Process Audit Log'[Org_code]))

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here
Anonymous
Not applicable

Hello @amitchandak -- thank you for the response. Would these measures be able to be added to a matrix table, or is that not possible? It appears to not let me add them as matrix table only accepts one column field, [Process Step] and doesn't let me use the new measure at all

@Anonymous , Are you trying for a hybrid table 

if you are looking for a Hybrid display with Matrix Column and measure
https://community.powerbi.com/t5/Community-Blog/Creating-a-custom-or-hybrid-matrix-in-PowerBI/ba-p/1354591
https://community.powerbi.com/t5/Quick-Measures-Gallery/The-New-Hotness-Custom-Matrix-Hierarchy/m-p/963588#M428

vote for Hybrid Table
https://ideas.powerbi.com/ideas/idea/?ideaid=9bc32b23-1eb1-4e74-8b34-349887b37ebc

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.