Forum Discussion
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), Col G (Kupon). I was able to find exact matches by grouping in query. I was able to fill Exact_Kupon kolumn the same way. Still have plenty of unmatched rows. I need to create new Col returning Index number of nearest Col F (Powierzchnia) value meeting Col B and Col D criteria. I was trying find matching row in Excel, but the outcome depends on sorting. Is there a way to do with DAX?
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] )
9 Replies
Rabarbar So generally you would do something like a MAXX(FILTER('Table', ...), [Column]). Hard to be more specific with the information provided. You might also try LOOKUPVALUE but I tend to have better luck with MAXX(FILTER.
Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.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
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] )
