Forum Discussion
Nearest value based on other columns values
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, thanx for your replay,
In Dax, to find exact match I could use
= CALCULATE(
MAXX(
SUMMARIZE(WYSZUKIWARKA;WYSZUKIWARKA[Wersja reklamy];WYSZUKIWARKA[Format_mm];WYSZUKIWARKA[Kupon];
"TEMP";MAX(WYSZUKIWARKA[Index]))
;[TEMP]);
ALLEXCEPT(WYSZUKIWARKA;WYSZUKIWARKA[Wersja reklamy];WYSZUKIWARKA[Format_mm];WYSZUKIWARKA[Kupon])
To find nearest match I need to do the similar search omitting WYSZUKIWARKA[Format_mm] condition to catch the rest of [Wersja reklamy] and [Kupon] matching records. But instead of returning highiest Index value I need to find closest match based on kolumn [Powierzchnia], and finally take the highiest Index value. It's too much for my DAX skills 😞
On scr below, red dots mark criteria and outcome [Exact] which is the latest index with all criteria met.
Marked cyan is the same search with no exact result. In that case I need to search for closest to cyjan box value (55600) among boxed green array (criteria [Wersja reklamy] = 3w1 and [Kupon] = -1 met, [Format_mm] irrelevant). I should get latest index with 55350 value in [Powierzchnia] Column. [Powierzchnia] is area of [Format_mm], I'm searching for most similar area in case when I got no exact format aviable.
| Index | Wersja reklamy | Cena | Skrot | Format_mm | Powierzchnia | Kupon | Exact | Exact inny kupon | Exact_Kupon |
| 15 | 3w1 | -1 | FP | 195x258 | 50310 | -1 | 0 | 6096 | 3435 |
| 131 | 3w1 | -1 | FP | 205x270 | 55350 | -1 | 3435 | 0 | 0 |
| 362 | 3w1 | -1 | FP | 200x278 | 55600 | -1 | 0 | 0 | 3435 |
| 470 | 3w1 | -1 | FP | 205x280 | 57400 | -1 | 0 | 0 | 3435 |
| 627 | 3w1 | 87 | FP | 205x270 | 55350 | Kwota | 0 | 7761 | 0 |
| 656 | 3w1 | -1 | FP | 200x273 | 54600 | -1 | 0 | 0 | 3435 |
| 3435 | 3w1 | -1 | FP | 205x270 | 55350 | -1 | 0 | 7761 | 0 |
| 3742 | 3w1 | -2 | FP | 205x270 | 55350 | -2 | 7761 | 0 | 0 |
| 3908 | 3w1 | -2 | FP | 192x258 | 49536 | -2 | 0 | 0 | 7761 |
| 4283 | 3w1 | -2 | FP | 205x270 | 55350 | -2 | 7761 | 0 | 0 |
| 4405 | 3w1 | -2 | FP | 205x270 | 55350 | -2 | 7761 | 0 | 0 |
| 4406 | 3w1 | -2 | FP | 205x270 | 55350 | -2 | 7761 | 0 | 0 |
| 4629 | 3w1 | -2 | FP | 147x210 | 30870 | -2 | 4901 | 0 | 0 |
| 4630 | 3w1 | -2 | FP | 205x270 | 55350 | -2 | 7761 | 0 | 0 |
| 4800 | 3w1 | -2 | FP | 200x277 | 55400 | -2 | 0 | 0 | 7761 |
| 4901 | 3w1 | -2 | FP | 147x210 | 30870 | -2 | 0 | 0 | 7761 |
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
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].