cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
cril19
Frequent Visitor

Ignore measure in bar chart if it is blank or 0

Hi all. I have a bar chart with a legend that I'd like to show up to five different measures. For each of the measures, the user can enter a dollar figure into a separate "Between" slicer. A measure then calculates the minimum of this slicer (we're hiding the high end of the Between slicer) and that measure is placed in the visual.

 

If the user enters a 0 into one of the Between slicers or leaves it blank, then I'd like the bar chart to ignore that measure by removing it from the legend and removing the space for it on the bar chart. I've tried using the visual filters and can't make changes to the filters for these measures. I also don't see an option to uncheck "show data when blank" for these measures. Not sure what the best approach is here. Thanks in advance for your thoughts!

3 REPLIES 3
Super User IV
Super User IV

Re: Ignore measure in bar chart if it is blank or 0

@cril19 not fully sure what you are trying to achieve, can you share some screen shot, measure you are using and what your end goal is?






Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





cril19
Frequent Visitor

Re: Ignore measure in bar chart if it is blank or 0

Thanks for the reply @parry2k. Below is a genericized version of what we're trying to do. Current state is that a user can enter numbers into the left boxes under Sample Input 1, 2, and 3. The boxes with 50000 would be hidden, but I've left them in this screenshot to better explain our methodology.  If a user types "19" into the first box, we then have a measure that calculates the min of the 19 and the 50000 - essentially making the measure 19. That measure then gets plotted on the chart. However, if a user enters a 0 (such as in Sample Input 3 below), we'd like the measure to be ignored - not listed in the legend and no space/gap in the middle of the chart. Current visual is on the left below and the desired output is on the right. Depending on the user, they may enter data into only Sample Input 1, into both Sample Input 1 and 2, or into all three Sample Inputs.

 

Does this help clarify? If not, what other info would be helpful.

 

 Sample.jpg

Super User IV
Super User IV

Re: Ignore measure in bar chart if it is blank or 0

@cril19 got it. I think the way you are solving it creating measure for sample 1, 2, 3 whereas if we have unpivtod data set and the measure value is calculated that will work

 

for example, let's we have table like this

 

Category

Sample1

Sample2

Sample3

 

And now add measure

 

Measure = 
VAR __category = MAX ( Table[Category] )
RETURN
SWITCH ( __category,
"Sample1", IF( [Slicer1 Value] <> 0, [Your Slicer 1 measure] ),
"Sample2", IF( [Slicer2 Value] <> 0, [Your slicer 2  measure] ),
"Sample3", IF( [Slicer3 Value] <> 0, [Your slicer 2  measure] )
)

So basically if slicer value is selected 0, measure will return blank otherwise whatever condition you want.

 

Now on bar chart, put Category on Legend and Measure on value and I expect it to work






Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors