Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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?
Solved! Go to Solution.
@didakk here is the pbix file for you.
https://file.io/KY09NDhDk5iF
Proud to be a Super User!
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.
Proud to be a Super User!
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
@didakk here is the pbix file for you.
https://file.io/KY09NDhDk5iF
Proud to be a Super User!
User | Count |
---|---|
53 | |
35 | |
20 | |
15 | |
14 |
User | Count |
---|---|
94 | |
76 | |
36 | |
22 | |
18 |