Forum Discussion

ARomain57's avatar
ARomain57
Frequent Visitor
2 years ago
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.

 

 

I created two columns in Powerbi, a Referral Milestone to DateTime and one with ABS, in case the closest time falls before the milestone. 

 

 

I am struggling with my equation. I want the Final Value with the closest DateTime within 180 minutes or less after the Referral Date or anytime before the Referral Datetime. So, anything from -x to 180. Different attributes have different conditions. Another attribute, MAP, is -x to 60 minutes. 

 

The value I need from the table above is 7.38, from the -6647 row, since that is the nearest value.

 

I created a Measure that seems to work, and when I plug in my filters, it returns the value I want, regarding the minumum time between the Referral Date and DateTime. It also filters the other attributes as expected.

 

MinTimeBetween Referral Milestone =
CALCULATE(
MIN('Final Table'[Referral Milestone to DateTime ABS]),
ALLEXCEPT('Final Table', 'Final Table'[Referral ID], 'Final Table'[Attribute], 'Final Table'[Referral Milestone to DateTime]))
 
 
The problem is my measure for the final value. Not sure what I am doing wrong, but it is returning a value of 7.47. I think it is returning the Max value between all values that are -x to 180. Not sure what I am doing wrong though
 
ValueAtMinTimeBetween Referral =
CALCULATE(
    MAX('Final Table'[Final Value]),
    FILTER(
        'Final Table',
        'Final Table'[Referral Milestone to DateTime ABS] =  [MinTimeBetween Referral Milestone]
        && 'Final Table'[Referral ID] = MAX('Final Table'[Referral ID])
    )
)
 

 

Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    2 years ago

    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

     

1 Reply

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    2 years ago

    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