AND logic in a slicer (when multi-selected)

[AnthonyJoseph]

Hi,

I am struggling to display category names based on the year slicer. When I select two different years from slicer then table should display the list of common categories that are present in all the selected years. I have pasted the sample data below:

Category	Year	unit
Apple	2022	100
Apple	2021	200
Apple	2020	500
Papaya	2021	100
Papaya	2020	40
Cucumber	2022	150
Cucumber	2021	250

In this example when I select the year 2021 the output should be all the categories that have year 2021 i.e. Apple, Papaya and Cucumber.

When year 2020 and 2021 are selected, then table should be display Apple and Papaya.

When year 2022 and 2021 are selected, then table should be display Apple and Cucumber.

So basically, based on the years selected my visual should display the list of common categries in a table. 

Appreciate any help on how to achieve this.

Thanks,

AnthonyJoseph

[CNENFRNL · Accepted solution]

For fun only, a showcase of powerful Excel worksheet formula,

[AnthonyJoseph]

@CNENFRNL I'm trying to use this logic in another graphs(ex: top 5 common categories in the selected years), which doesnt give the correct output. I guess it might be because of using ALLSELECTED function. I observed that when this calculation is used in graph (not table) by default all values in the year columns are  being considered as selectedvalues i.e. ALLSELECTED function is not keeping fitlers applied, is there any other work around that will work? 

Appreciate your inputs.

Thanks,

AnthonyJoseph