Getting value A based of max of values B, if value C is...

[BasC]

This is the way the data is built up. All irrelevant data is blurred.

Data model viewer - 3 tables

Each [ritdossier] has multiple [dossier] values. Each [dossier] has multple [T&T_kode_order] and [tracdatum_order] and [tractijd_order] values.

But each [dossier] only has 1 [rijduur] value.

What I want is the value [rijduur] , of the Max([tractijd_order]) of that [ritdossier], if the [T&T_kode_order] = 'GETEKEND' and [tracdatum_order] = [KDatum] .

This formula works and gives me the right value for [tractijd_order]:

max(
      if(
      		tracdatum_order = KDatum and [T&T_kode_order]='GETEKEND',
          	tractijd_order
      )
)

But I just can't figure out how to connect this to [rijduur].

I've been stuck with this one for ages, despite reading here a lot and trying out a bunch of things. Hopefully I've explained it somewhat understandably. Thanks very much in advance for you help.

[Kushal_Chawda]

@BasC  try below

=Firstsortedvalue
(
[rijduur] 
,
-if
   (
    tracdatum_order = KDatum and [T&T_kode_order]='GETEKEND',
    tractijd_order
   )
)

[BasC]

@Kushal_ChawdaThanks for the quick reply, I appreciate it! I think I might have tried this expression at some point. But it doesn't work. I'm getting Null values unfortunately.

[Kushal_Chawda · Accepted solution]

@BasC  try with distinct if still does not work then suggest you to create mockup data with expected output

=Firstsortedvalue
( distinct 
[rijduur] 
,
-if
   (
    tracdatum_order = KDatum and [T&T_kode_order]='GETEKEND',
    tractijd_order
   )
)

[BasC]

Ah yes, that seems to have worked! Thank you so much!