Lookup Max Value from another table while keeping Report Filter

[Anonymous]

Hello!

I am trying to look up the maximum "utilization category" in Table 1 for each "item" and bring it into Table 2 as a calculated column to use as a slicer. The challenge I am running into is that the report will have a Year slicer and I need to specifically pull in the maximum Utilization Category within the filtered year range.

Table 1 is structured as below:

Item	Year	Utilization Category
1	2018	4
1	2019	2
1	2020	1
2	2018	1
2	2019	3
2	2020	2

Table 2 is structured as below. I have left out unnecessary columns, but know that the Item column may have duplicate item numbers.

Item

1

1

2

2

For example, if the Year is filtered from 2018-2019, the resulting column in  Table 2 would be:

Item	Max Utilization Category
1	4
1	4
2	3
2	3

If the filter was from 2019-2020, the resulting table would be

Item	Max Utilization Category
1	2
1	2
2	3
2	3

Is this possible? Thanks in advance for any help!

[V-pazhen-msft · Accepted solution]

@Anonymous 
You may create the following column in table 2, but there is not enough details in table 2, the unnecessary columns are important when you create the relationship between the two tables. Please add more to the table 2 sample. You should have a year column since you want to filter by year.

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , how table 1 and table 2 are connected ??