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] )

Greg_Deckler's avatar
Greg_Deckler
Community Champion
3 years ago

Rabarbar Not certain I fully understand but maybe this. PBIX is attached below signature.

Closest = 
    VAR __P = MAX('WYSZUKIWARKA'[Powierzchnia])
    VAR __W = MAX('WYSZUKIWARKA'[Wersja reklamy])
    VAR __K = MAX('WYSZUKIWARKA'[Kupon])
    VAR __Table = 
        ADDCOLUMNS(
            FILTER(ALL('WYSZUKIWARKA'),[Wersja reklamy] = __W && [Kupon] = __K),
            "__Diff", [Powierzchnia] - __P
        )
    VAR __Min = MINX(__Table, [__Diff])
    VAR __Result = MAXX(FILTER(__Table,[__Diff] = __Min), [Index])
RETURN
    __Result

 

Rabarbar's avatar
Rabarbar
Frequent Visitor
3 years ago

Greg_Deckler, thank you very much again. It seems I'm not good at explaining my needs. The result is not as expected. Eg. I need the outcome in [Closest] row 4800 to be 4630 (as it is the latest index of row with [Powierzchnia] value closest to 55400 from row 4800. Now it gives 4901, which is not closest [Powierzchnia].