Get the Category in a Calculated column based on conditions/filters on other columns

[didakk]

Hi everyone,

I'm struggling figuring out how to solve this:

I have the following table

Channel - MMM Optimizer	Tipo	Group - MMM Optimizer	PAIS	Year	Real_Group	Rank	Desired Column
OTV	%_Vol	Media	GT	2019	Media	1	OTV
OTV	%_Vol	Media	GT	2020	Media	1	OTV
OTV	%_Vol	Media	GT	2021	Media	1	OTV
OTV	%_Vol	Media	GT	2022	Media	1	OTV
Social Media	%_Vol	Media	GT	2019	Media	2	Social Media
Social Media	%_Vol	Media	GT	2020	Media	2	Social Media
Display	%_Vol	Media	GT	2021	Media	2	Social Media
Social Media	%_Vol	Media	GT	2022	Media	2	Social Media
PTV	%_Vol	Media	GT	2019	Media	3	Display
Display	%_Vol	Media	GT	2020	Media	3	Display
Social Media	%_Vol	Media	GT	2021	Media	3	Display
Display	%_Vol	Media	GT	2022	Media	3	Display
Search	%_Vol	Media	GT	2019	Media	4	Search
PTV	%_Vol	Media	GT	2020	Media	4	Search
Search	%_Vol	Media	GT	2021	Media	4	Search
Search	%_Vol	Media	GT	2022	Media	4	Search
Youtube	%_Vol	Media	GT	2019	Media	5	PTV
Search	%_Vol	Media	GT	2020	Media	5	PTV
PTV	%_Vol	Media	GT	2021	Media	5	PTV
PTV	%_Vol	Media	GT	2022	Media	5	PTV
Display	%_Vol	Media	GT	2019	Media	6	Youtube
Youtube	%_Vol	Media	GT	2020	Media	6	Youtube
Youtube	%_Vol	Media	GT	2021	Media	6	Youtube
Youtube	%_Vol	Media	GT	2022	Media	6	Youtube
ABOX	%_Vol	Media	GT	2019	Media	7	ABOX
ABOX	%_Vol	Media	GT	2020	Media	7	ABOX
ABOX	%_Vol	Media	GT	2021	Media	7	ABOX
ABOX	%_Vol	Media	GT	2022	Media	7	ABOX

The "Desired Column" must be calculated based on the following criteria:

The user selects a Year in a slicer,  in this case "2022". The desired column must have on each row the Channel which had that RANK, in that Year (2022), in that Real_Group (Media), in that Country (GT).

I.E: 

in 2019, 2020, 2022 Social Media Channel ranked 2nd. In 2021 display was 2nd. If the user selects 2022, the Desired Column must show Social Media. If selects 2021 must show Display

Desired Column output when Selected Year =  2022

Channel - MMM Optimizer	Tipo	Group - MMM Optimizer	PAIS	Year	Real_Group	Rank	Desired Column
Social Media	%_Vol	Media	GT	2019	Media	2	Social Media
Social Media	%_Vol	Media	GT	2020	Media	2	Social Media
Display	%_Vol	Media	GT	2021	Media	2	Social Media
Social Media	%_Vol	Media	GT	2022	Media	2	Social Media

Desired Column output when Selected Year =  2021

Channel - MMM Optimizer	Tipo	Group - MMM Optimizer	PAIS	Year	Real_Group	Rank	Desired Column
Social Media	%_Vol	Media	GT	2019	Media	2	Display
Social Media	%_Vol	Media	GT	2020	Media	2	Display
Display	%_Vol	Media	GT	2021	Media	2	Display
Social Media	%_Vol	Media	GT	2022	Media	2	Display

There are more countries and more Real_Groups. This should be done by Real_Group, By country taking into account the Year selected and the rank 😞

I don't know how to proceed. May you please help me out whith this?

[rubayatyasmin]

Hi, @didakk 

first of all calculated columns don't handle users selection like slicer selection. You need to use measure for that. 

Now, I tried to reproduce your problem, You need to add rank as your slicer. and you do want the columns to interact within the visual dynamically. I think you need to create another calculated table that will work on your row selection. 

if this is the solution you want then I can provide you the pbix or the measures. Let me know if that helps. 

[didakk]

Hi @rubayatyasmin,

Thanks for your response.  I think this might help a lot. May you please share the PBI or the measure to do testing?

Thank you very much

[rubayatyasmin · Accepted solution]

@didakk here is the pbix file for you. 

https://file.io/KY09NDhDk5iF 

[didakk]

Thanks @rubayatyasmin