topic Re: Nearest value based on other columns values in DAX Commands and Tips

Nearest value based on other columns values

Rabarbar — Sat, 04 Mar 2023 15:24:49 GMT

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?

Re: Nearest value based on other columns values

Greg_Deckler — Sat, 04 Mar 2023 16:56:13 GMT

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.

Re: Nearest value based on other columns values

Rabarbar — Sat, 04 Mar 2023 17:27:26 GMT

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

Re: Nearest value based on other columns values

Greg_Deckler — Sat, 04 Mar 2023 18:04:45 GMT

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

Re: Nearest value based on other columns values

tamerj1 — Sat, 04 Mar 2023 18:59:29 GMT

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

Re: Nearest value based on other columns values

Rabarbar — Sat, 04 Mar 2023 18:51:58 GMT

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

Re: Nearest value based on other columns values

tamerj1 — Sun, 05 Mar 2023 08:20:54 GMT

Hi Rabarbar

have you tried my proposed solution?

Re: Nearest value based on other columns values

Rabarbar — Sun, 05 Mar 2023 08:45:42 GMT

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

Re: Nearest value based on other columns values

Rabarbar — Sun, 05 Mar 2023 11:59:39 GMT

tamerj1, confirm, it works flawlessly! Thanks!

Re: Nearest value based on other columns values

Rabarbar — Sun, 05 Mar 2023 21:34:39 GMT

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