Forum Discussion
Lookup Max Value from another table while keeping Report Filter
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!
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.
2 Replies
Anonymous , how table 1 and table 2 are connected ??
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.
