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, all! I have put together a stacked visual that shows a request count across different Subjects with a legend that shows the requests as one of three different categories (for the sake of simplicity, let's say Colors: "Green," "Yellow," or "Red"). For the purpose of this specific request, the visual is a stacked bar chart (percentage) where the axis is "Subjects" and the legend is the Color category.
I am trying to create a filter on this visual so that it ONLY shows Subjects that have an aggregate count of 50 requests or more. Specifically, as we only want to look at Subjects that have received a higher volume of Requests.
Applying this filter with Subjects as the axis without the legend is a simple task. However, the moment I apply a legend (Colors) and make this a stacked bar chart the filters are now being applied to the created measures for the individual Color portions for each Subject rather than the aggregate number.
That is, if Subject A has 52 Green, 54 Yellow, and 32 Red items the visual will now only pick up the Green and Yellow portions for that Subject. Similarly, if Subject B has 49 of each Color, it will now be completely excluded despite the aggregate count being above 50 because each legend component is below 50. Is there any way to apply a visual-level filter that keys to the Subject / Axis Item Aggregate count while maintaining the legend for a % stacked bar chart, instead of filtering the legend components for that Subject?
Or, would the only feasible solution here be to create a separate table with a list of all Subjects and a column with a conditional label (“Include” or “Exclude”) based on the value/measure of Requests for that Subject? This is likely not the ideal solution, as the list of Subjects may change as new ones are added. Furthermore, we would also want to create the same visual with similar total Request count thresholds and legends for other groupings / Axes (e.g., Region of Request Origin; Calendar Year Quarter) that may also dynamically change to include newer items over time. Unless we can make the tables on which these conditional labels would be applied dynamically populate with the list of all Subjects (or other Column values) that appear in the connected data set when it is refreshed?
Any help would be much appreciated!
Solved! Go to Solution.
Was able to figure this out after utilizing CALCULATE and ALLEXCEPT in a new measurement.
Was able to figure this out after utilizing CALCULATE and ALLEXCEPT in a new measurement.
Please what was the solution?
I'm having the same problem but can't find a solution using calculate with allexcept. Can you share your formula? Thanks!
It's hard to picture/visualize this and it's a lot of words.
Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
@Greg_Deckler Sorry about that. Hopefully this might be a bit simpler.
I was able to somewhat achieve the desired effect by creating a separate table with a column that counted the requests based on the Subject name, and then adding another column with a conditional "Include" value based on the returned column value (and filtering the visual to only show items for Subjects marked as "Include"). However, this was not 'dynamic' in the sense that its Request count column is not impacted when a slicer is used to filter to a specific region or group of regions.
Sample of Individual Data Point
Subject | Color Code | Region | Date Submitted |
Subject A | Green | Northeast | 12/15/2019 |
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 |
---|---|
112 | |
97 | |
84 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |