INDEX and AGGREGATE FUNCTION in Power BI

Question

I have a two tables are data and report.

In data table the following columns are Size A, Size B and Size C, Type and Rank.

In data table I created rank for each type based on the sizes. The purpose of the rank column were multiple matches for same size in this case the rank column will help to decide exact type were match more than one.

In report table the following columns are Size A, Size B and Size C.

In both table the Size A, Size B and Size C columns are common/relationship.

I am trying find out the appropriate type according to the Size A, Size B and Size C from data table into report table.

Data:

TYPE	SIZEA	SIZEB	SIZEC	RANK
A6	420	600	440	11.00
A4	640	600	480	9.00
A5	890	1100	1330	2.00
A6	1335	1100	2350	1.00
A7	890	1100	390	5.00
A8	890	1100	530	3.00
A9	670	1100	540	4.00
A10	670	1100	440	6.00
A11	320	1100	440	10.00
A12	600	400	400	12.00
A13	800	600	400	8.00
A14	1000	600	500	7.00

Report:

SIZEA	SIZEB	SIZEC	DESIRED RESULT-TYPE
400	300	140	A12
			A12
250	250	160	A12
600	400	285	A12
400	300	150	A12
280	230	170	A12
320	320	320	A12
320	320	320	A12
600	400	140	A12
400	300	140	A12
400	300	140	A12
370	320	340	A12
320	240	250	A12
300	200	90	A12
400	290	140	A12

I am applying following formula in report table in order to get the appropriate type according to the Size A, Size B and Size C

=INDEX(DATA!$D$2:$D$16,AGGREGATE(15,6,(ROW(DATA!$H$2:$H$16)-1)/(DATA!$H$2:$H$16=1/(1/MAX(((DATA!$E$2:$E$16>=$B3)*(DATA!$F$2:$F$16>=$A3)+(DATA!$E$2:$E$16>=$A3)*(DATA!$F$2:$F$16>=$B3)>0)*(DATA!$G$2:$G$16>=$C3)*DATA!$H$2:$H$16))),1)) 

How can I apply the same logic in Power BI? Any advise please.

I am looking for new calculate column options. Herewith share the Excel file for your reference

https://www.dropbox.com/scl/fi/iq0gteeyazrg79q7a4tb1/AUTO-MODIFY-REQ.xlsx?dl=0&rlkey=nyyerjsg7if2dz30z9iqo6kdc

Answer 1

Here is another formula that will return the same result as per your current formula, although it is still a long formula, but more easily to understand:

=INDEX($D$2:$D$13,MATCH(MAX(IF($G$2:$G$13<$L3,0,
IF((IF($E$2:$E$13<$K3,0,1)*IF($F$2:$F$13<$J3,0,1))+(IF($E$2:$E$13<$J3,0,1)*IF($F$2:$F$13<$K3,0,1))>0,1,0))
*$H$2:$H$13),$H$2:$H$13,0),1)

So I will break it down to explain how do it working:

Part 1: This if formula is checking comparison of Size C, if your data is lower than than table, the value for the Type will be zero therefore will not be considered at all because zero times any amount will be zero

(IF($G$2:$G$13<$L3,0

Part 2: This part is will first checking comparison of Size A vs Size B, if lower than table than return 0 and apply for other comparison, at then end using If at the beginning to reset the value so than the final value will be 0 or 1

IF((IF($E$2:$E$13<$K3,0,1)*IF($F$2:$F$13<$J3,0,1))+(IF($E$2:$E$13<$J3,0,1)*IF($F$2:$F$13<$K3,0,1))>0,1,0)

Part 3: From the value you calculate, if 0 times any ranking will be zero, so from none zero ranking such as 12 or 9, it will get the max value from the list, most of them are 12

Max((.....)*$H$2:$H$13)

Part 4: Index Match - Finally it is same as your formula first part, but you are using Index row which make it difficult to understand

INDEX($D$2:$D$13,MATCH(Max(...))

A shorter version of your formula by removing unnecessary portion:

=INDEX($D$2:$D$13,MATCH(MAX(IF($G$2:$G$13<$L4,0,
IF(($E$2:$E$13>=$K4)*($F$2:$F$13>=$J4)+($E$2:$E$13>=$J4)*($F$2:$F$13>=$K4)>0,1,0))*$H$2:$H$13),
$H$2:$H$13,0),1)

Answer 2

You can add a colum to your report table like:

Desired = LOOKUPVALUE(DataR[TYPE],DataR[RANK], MAXX(FILTER(DataR, DataR[SIZEA] >= ReportR[SIZEA] && DataR[SIZEB] >= ReportR[SIZEB] && DataR[SIZEC] >= ReportR[SIZEC]), DataR[RANK]))

It first filters the table to find the row where the sizes are more or equal to the report row, selects the rank and then does a lookup to the type.

Result: