Forum Discussion
Solved
Trying to get Minimum Value per ID from datetime
My goal is to get the value at the closest time between the Referral Date milestone and the DateTime, given some conditions I will discuss later. I have a table below that shows an example. ...
ARomain57 I would the following, it's a classic Lookup Min/Max pattern: Lookup Min/Max - Microsoft Fabric Community
Measure = VAR __ID = MAX( 'Final Table'[Referral ID] ) VAR __Attribute = MAX( 'Final Table'[Attribute] ) VAR __Lowest = MINX( FILTER( ALL( 'Final Table' ), [Referral ID] = __ID && [Attribute] = __Attribute ), [Referral Milestone to DateTime ABS] ) VAR __Final = MINX( FILTER( ALL( 'Final Table' ), [Referral Milestone to DateTime ABS] = __Lowest && [Referral ID] = __ID && [Attribute] = __Attribute ), [Final Value] ) RETURN __Final
Greg_Deckler
Community Champion
Community ChampionARomain57 I would the following, it's a classic Lookup Min/Max pattern: Lookup Min/Max - Microsoft Fabric Community
Measure =
VAR __ID = MAX( 'Final Table'[Referral ID] )
VAR __Attribute = MAX( 'Final Table'[Attribute] )
VAR __Lowest = MINX( FILTER( ALL( 'Final Table' ), [Referral ID] = __ID && [Attribute] = __Attribute ), [Referral Milestone to DateTime ABS] )
VAR __Final = MINX( FILTER( ALL( 'Final Table' ), [Referral Milestone to DateTime ABS] = __Lowest && [Referral ID] = __ID && [Attribute] = __Attribute ), [Final Value] )
RETURN
__Final