Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
JManfredini
Regular Visitor

Is there a way to display a category count as "less than #" if the count is less than a value?

Hello wonderful BI-ers, 

 

I am working with FERPA protected data and cannot display the count of a demographic category if the count is very small, because the person/people in that category might be able to be identified. 

 

So instead, I want to display the count of a category as "less than 5" if the count is less than five, but retain the numeric count values for categories where the count is over five. This count would be in a tooltip. In other words: when I hover over a column in my bar chart, I want to display a text value if the count is under a threshold, and a numeric value if the count is over a threshold. 

 

Here is a sample of the data (not actual numbers):

 

YearRaceCount
2020White189
2020Black27
2020

Asian

89
2020Other2
2019White234
2019Black54
2019Asian4
2019Other6

 

So in this case, when I hover over the bar for [Year=2020  Race=Other], and [Year=2019 Race=Asian], Instead of the tooltip displaying "2" and "4" respecively, I see "Less than 5" (or "< 5"). 

 

I have a tooltip set up right now displaying counts in categories, what I'm asking is about displaying those values as text based on a condition. 

 

Is this possible? 

1 ACCEPTED SOLUTION
d_gosbell
Super User
Super User

I don't believe this is currently possible with the default tips in the on-prem report server unless you used a custom tooltip with a different measure.

 

If you are deploying to the Power BI service you could use the new dynamic format strings feature Create dynamic format strings for measures in Power BI Desktop - Power BI | Microsoft Learn and create a format string like 

 
if( SELECTEDMEASURE() < 5, "Less than 5", "#,0.00")

Otherwise you could create and additional measure with an expression like:
 
[Reported Person Count] =  if( [Person Count] < 5, "Less than 5", [Person Count])
 
But you would probably also have to create a custom tooltip as the default one will always show the value for the original measure and you cannot add the measure above to a barchart as it cannot graph the "Less than 5" text.

View solution in original post

2 REPLIES 2
d_gosbell
Super User
Super User

I don't believe this is currently possible with the default tips in the on-prem report server unless you used a custom tooltip with a different measure.

 

If you are deploying to the Power BI service you could use the new dynamic format strings feature Create dynamic format strings for measures in Power BI Desktop - Power BI | Microsoft Learn and create a format string like 

 
if( SELECTEDMEASURE() < 5, "Less than 5", "#,0.00")

Otherwise you could create and additional measure with an expression like:
 
[Reported Person Count] =  if( [Person Count] < 5, "Less than 5", [Person Count])
 
But you would probably also have to create a custom tooltip as the default one will always show the value for the original measure and you cannot add the measure above to a barchart as it cannot graph the "Less than 5" text.

Thanks for this advice! Unfortunately I don't have Desktop, only Report Service. This is a cool feature (if only I could use it). 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.