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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Anonymous
Not applicable

Return Max Date of Grouped ID Filtered by Value in Another Column

Hi, folks,

 

First of all, this forum is amazing.

 

I want to create the "Max Date by Value" column, which includes the largest date for each ID group that has the value of 2.

 

Here is what I want such a table to look like.

 

IDDateValueMax Date by Value
111/1/201545/1/2013
15/1/201455/1/2013
15/1/201325/1/2013
211/1/20121Null
31/1/201321/1/2016
36/1/201051/1/2016
311/1/201641/1/2016
31/1/201621/1/2016
37/1/201771/1/2016

 

Here is the code I am playing with that is not working:

Max Term 2 Date =
var a =CALCULATE(MAX('Table'[Date]),ALLEXCEPT('Table', 'Table'[ID]), FILTER('Table', 'Table'[Value]=2))
var b =CALCULATE(MAX('Table'[Date]), FILTER('Table', 'Table'[ID]=EARLIER('Table'[ID])),'Table'[Date]=a)
Return
b
 
Thoughts on how to proceed?
 
Thanks!
1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

Try

Column

Max Date by Value = if([Value]=2, blank(),maxx(filter('Table', [ID]=earlier([ID])),[Date]))

 

Measure

Max Date by Value = calculate(max(Date),ALLEXCEPT('Table', 'Table'[ID]),[Value]<>2)
Max Date by Value = if(max([Value]=2),blank(),calculate(max(Date),ALLEXCEPT('Table', 'Table'[ID])))

View solution in original post

3 REPLIES 3
amitchandak
Super User
Super User

Try

Column

Max Date by Value = if([Value]=2, blank(),maxx(filter('Table', [ID]=earlier([ID])),[Date]))

 

Measure

Max Date by Value = calculate(max(Date),ALLEXCEPT('Table', 'Table'[ID]),[Value]<>2)
Max Date by Value = if(max([Value]=2),blank(),calculate(max(Date),ALLEXCEPT('Table', 'Table'[ID])))

Anonymous
Not applicable

Thank you, @amitchandak!

Anonymous
Not applicable

I think I just figured it out.

 

CALCULATE(MAX('Table'[Date]), FILTER('Table', 'Table'[ID]=EARLIER('Table'[ID])),'Table'[Value]=2)
 
I was overthinking it, but there may be a better way.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.