Index and MIN value based on the two tables

[Saxon10]

Hi,

I have two tables are data and report.
"The data table contains the 'Area' and 'Priority' columns.
The Report table contains columns such as 'Item,' 'Area,' and 'Priority'.
If an item has multiple priorities or areas, I aim to retrieve the minimum priority value within that area. When a code has a unique priority, I will return that same value.
Example:
Item 3663 exhibits multiple priorities and areas, namely DEL, CHE, and LKN, with priorities of 3, 2, and 7, respectively. As a result, the selected area is CHE.
Similarly, for Item 166, having multiple priorities and areas (CHE and RCB) with priorities 2 and 6, the chosen area is CHE. Additionally, Item 489 features multiple priorities and areas (DEL and GT) with priorities 3 and 8; consequently, the selected area is DEL. For all other items, since they possess a unique value, the same value is returned."
I currently utilize a specific Excel formula "=INDEX($I$2:$I$11,MIN(MATCH(FILTER($B$2:$B$13,$A$2:$A$13=$A2),$I$2:$I$11,0)))" to obtain the desired outcome. How can I replicate the same process in Power BI using DAX within the 'New calculated column.

Report:

Item	Area	Priority	Desired Result-1
166	CHE	2	CHE
166	RCB	6	CHE
489	DEL	3	DEL
489	GT	8	DEL
540	CHE	2	CHE
541	CHE	2	CHE
3519	LKN	7	LKN
3663	RCB	6	CHE
3663	DEL	3	CHE
3663	CHE	2	CHE
3663	LKN	7	CHE
3815	LKN	7	LKN

Data:

Priority	Area
1	MI
2	CHE
3	DEL
4	SRH
5	KKR
6	RCB
7	LKN
8	GT
9	KPJ
10	RR

Excel and PBI attached for your reference.

https://www.dropbox.com/scl/fi/aze3qy5bndkwm1wiax4xr/example_1.xlsx?rlkey=agpici6e753nkycjrs8d9c0ko&...

https://www.dropbox.com/scl/fi/er15yl2ogse657zbslc06/ZC.pbix?rlkey=2ng91fmcrxb6meycrw6w3trkj&dl=0

[Ashish_Mathur · Accepted solution]

Hi,

This problem can be solved without the second table.  Use these calculated column formulas

Minimum priority number = CALCULATE(MIN(REPORT[Priority]),FILTER(REPORT,REPORT[Item]=EARLIER(REPORT[Item])))

Area of minimum priority = CALCULATE(MAX(REPORT[Area]),FILTER(REPORT,REPORT[Item]=EARLIER(REPORT[Item])&&REPORT[Priority]=EARLIER(REPORT[Minimum priority number])))

Hope this helps.

[Saxon10]

Thanks for your reply and support.