Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
Solved! Go to Solution.
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
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 @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
@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
@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].
User | Count |
---|---|
42 | |
28 | |
23 | |
18 | |
16 |
User | Count |
---|---|
54 | |
35 | |
18 | |
18 | |
15 |