Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello, I'm using a slicer that enables a user to "Show YTD" if he or she would like to view YTD comparisons across years instead of full year amounts. The slicer was created using a calculated column says "YTD" for YTD months. My problem is that my slicer only has one selection, and clearing it will allow the user to see full year amounts, but I would like a second selection that says "Full Year." Does anyone know a way to add a selection to a slicer that effectively clears the slicer? I would really like to have both "YTD" and "Full year" buttons and have it require single selection, so the user can clearly understand what he or she is looking at....I hope that makes sense and would really appreciate your help!!!
Thanks,
Jordan
Solved! Go to Solution.
@Anonymous
I am not sure if I understand your request clearly. Since I answered your another topic, I guess below is what you want.
I created some test data.
Then I created another selection table.
Then created some measures.
MAXMONTH = month(CALCULATE(max('tbl'[date]),all(tbl)))
full year = sum(tbl[value])
YTD = CALCULATE(sum(tbl[value]),FILTER(tbl,month(tbl[date])<=[MAXMONTH]))
Measure = IF(SELECTEDVALUE('Table'[selection])="YTD",[YTD],if(SELECTEDVALUE('Table'[selection])="Full Year",[full year]))
Now you can see the results when you click YTD or full year.
Hope this is helpful.
Proud to be a Super User!
@Anonymous
I am not sure if I understand your request clearly. Since I answered your another topic, I guess below is what you want.
I created some test data.
Then I created another selection table.
Then created some measures.
MAXMONTH = month(CALCULATE(max('tbl'[date]),all(tbl)))
full year = sum(tbl[value])
YTD = CALCULATE(sum(tbl[value]),FILTER(tbl,month(tbl[date])<=[MAXMONTH]))
Measure = IF(SELECTEDVALUE('Table'[selection])="YTD",[YTD],if(SELECTEDVALUE('Table'[selection])="Full Year",[full year]))
Now you can see the results when you click YTD or full year.
Hope this is helpful.
Proud to be a Super User!
@ryan_mayuThanks, Ryan. Would this mean I would need to create new YTD and Full Year measures for each metric? Since, the slicer will affect 20+ metrics across multiple tabs in the file, I was really hoping to avoid doing that, but it sounds like that might be the only option?
@Anonymous
Maybe we have other better solution. However, it's very hard to suggest you before being familar to your data structure and your data model. You'd better to share more detailed info or sample file
Proud to be a Super User!
@ryan_mayuI have a sample file. How would you like me to send it to you? I don't see the option to attach a file on this message?
@Anonymous
you can provide the link to download your file (e.g. onedrive).
Or you can provide some excel screenshots and make sure you also show the expected results in the screenshots.
Proud to be a Super User!
@ryan_mayu I don't have onedrive, so I'll try to show a different way. I have a slicer that uses a calculated column you helped me create to filter YTD metrics. (see below)
My problem is that my attempt to capture full year does not work. Since the YTD months are already named "Show YTD", my attempt to capture all months "Show full year" using <=12 only captures the months not already called "Show YTD."
Can you help me adjust the formula to pick up all months when "Show Full Year" is selected?
Thanks.
Jordan
@Anonymous
It looks like you are not using the measure, just showing texting.
Now you need to have two measures.
1. show YTD
2. show full year
Do these two measures work fine separately?
If so, please try the meaure
if selectedvalue()="Full year", fullyearmeasure, if selectedvalue()="YTD",YTD measure
Proud to be a Super User!
@ryan_mayu I'm trying to do it without creating a 2 new measures for every metric as that would require many different slicers to be able to go back and forth between YTD and Full year views. I will use the same strategy on several other tabs that have many more metrics, also. I really just need a way to have a selection for Full Year that clears the filter or includes all months. It's turned out to be incredibly hard as I've been searching for almost a month, now. 🙂
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |