Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Rabarbar
Frequent Visitor

Nearest value based on other columns values

Hi, I'm struggling to find a solution.

Rabarbar_0-1677939680816.png

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?

Rabarbar_1-1677941782845.png

 

1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

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

View solution in original post

9 REPLIES 9
tamerj1
Super User
Super User

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

Hi @Rabarbar 

have you tried my proposed solution?

@tamerj1, confirm, it works flawlessly! Thanks!

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

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

Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

Rabarbar_0-1677950280755.png

IndexWersja reklamyCenaSkrotFormat_mmPowierzchniaKuponExactExact inny kuponExact_Kupon
153w1-1FP195x25850310-1060963435
1313w1-1FP205x27055350-1343500
3623w1-1FP200x27855600-1003435
4703w1-1FP205x28057400-1003435
6273w187FP205x27055350Kwota077610
6563w1-1FP200x27354600-1003435
34353w1-1FP205x27055350-1077610
37423w1-2FP205x27055350-2776100
39083w1-2FP192x25849536-2007761
42833w1-2FP205x27055350-2776100
44053w1-2FP205x27055350-2776100
44063w1-2FP205x27055350-2776100
46293w1-2FP147x21030870-2490100
46303w1-2FP205x27055350-2776100
48003w1-2FP200x27755400-2007761
49013w1-2FP147x21030870-2007761

@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

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

Rabarbar_1-1677955884800.png

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors