Forum Discussion

Rabarbar's avatar
Rabarbar
Frequent Visitor
3 years ago
Solved

Nearest value based on other columns values

Hi, I'm struggling to find a solution. I need to search >10K data set for the latest row (highest Index value) meeting several criteria: Col B (Wersja reklamy), Col C (Cena), Col E (Format_mm)...
  • tamerj1's avatar
    tamerj1
    3 years ago

    Hi Rabarbar 

    please try

    =
    VAR CurrentPowierzchnia = WYSZUKIWARKA[Powierzchnia]
    VAR T1 =
    CALCULATETABLE (
    WYSZUKIWARKA;
    ALLEXCEPT ( WYSZUKIWARKA; WYSZUKIWARKA[Wersjareklamy]; WYSZUKIWARKA[Kupon] )
    )
    VAR T2 =
    FILTER ( T1; WYSZUKIWARKA[Powierzchnia] <> CurrentPowierzchnia )
    VAR T3 =
    TOPN ( 1; T2; ABS ( CurrentPowierzchnia - WYSZUKIWARKA[Powierzchnia] ); asc )
    RETURN
    MAXX ( T3; WYSZUKIWARKA[Index] )

tamerj1's avatar
tamerj1
Community Champion
3 years ago

Hi Rabarbar 

please try

=
VAR CurrentPowierzchnia = WYSZUKIWARKA[Powierzchnia]
VAR T1 =
CALCULATETABLE (
WYSZUKIWARKA;
ALLEXCEPT ( WYSZUKIWARKA; WYSZUKIWARKA[Wersjareklamy]; WYSZUKIWARKA[Kupon] )
)
VAR T2 =
FILTER ( T1; WYSZUKIWARKA[Powierzchnia] <> CurrentPowierzchnia )
VAR T3 =
TOPN ( 1; T2; ABS ( CurrentPowierzchnia - WYSZUKIWARKA[Powierzchnia] ); asc )
RETURN
MAXX ( T3; WYSZUKIWARKA[Index] )

tamerj1's avatar
tamerj1
Community Champion
3 years ago

Hi Rabarbar 

have you tried my proposed solution?

  • Rabarbar's avatar
    Rabarbar
    Frequent Visitor
    3 years ago

    Hi tamerj1 , got no idea how, but  I overlooked your answer. Thanks for your answer and reminder. It seems you nailed it - I'll double check it against all records and let you now

  • Rabarbar's avatar
    Rabarbar
    Frequent Visitor
    3 years ago

    tamerj1, confirm, it works flawlessly! Thanks!

    • Rabarbar's avatar
      Rabarbar
      Frequent Visitor
      3 years ago

      tamerj1, just letting you know, you've rocket jumped my work, thank you ♥