How to hide results that are below a given frequency when a table will be filtered?

[TM_Visual]

Summary: I would like to know if there is any way to have graphs and tables display  “<5 “ when a filter reduces the frequency of records down to that number.

I have a dataset of thousands of students with various pieces of information such as gender, the country they live in and the course that they study.I want to prepare a dashboard that allows members of the public to see this information and filter it to show, for example, how many women are studying Engineering, or how many people from Mexico study Art.

Currently I have prepared a report based on the raw data, with each student a row, each value in a new column. I have a couple of graphs and a table that displays relevant information, mainly counting the raw numbers.

A difficulty I have is that if someone filters down to a low level, they can potentially see information that is potentially identifying; for example by asking in particular about male Greeks studying Maths, they can see that there are two of them, and one was unsuccessful.

I know that I can change visuals to hide results below a certain threshold, but I would prefer to leave an indication that a change has been made, without specifying the exact figure.

Is there is any way to have graphs and tables display  “<5 “ when a filter reduces the number of records down to that level, rather than making the value invisible?

Thanks in advance for your advice.

[v-yulgu-msft]

Hi @TM_Visual,

Based on my understanding, you want to show the number of records that has been reduced by slicer filter, right? For example, there are total 8 rows in source table, after filtering, there existing 3 rows in table visual, so, you want to show 5, right?

If that is the case, you can refer to below formula to create a measure and show it in a card visual.

Count reduce =
CALCULATE ( COUNT ( Sheet1[Amount] ), ALL ( Sheet1 ) )
    - COUNT ( Sheet1[Amount] )

If I have something misunderstood, please elaborate what did you mean "have graphs and tables display  “<5 “ when a filter reduces the number of records down to that level, rather than making the value invisible" with an image to show your desired output.

Best regards,
Yuliana Gu

[TM_Visual]

Hi @v-yulgu-msft

Thanks for replying. Unfortunatley, this is not what I'm looking for.

I have a table that normally would look like

Year     Number
_______________
2014     21
2015     9
2016     2
2017     15

(please excuse the formatting)

I would like it to display:

Year     Number
_______________
2014     21
2015     <10
2016     <10
2017     15

I came up with a partial solution last night:

Number of offers = IF([Number]>9,
[Number],"<10")

Unfortunately this presents a new problem: the number is formatted to include decimal places:

Year     Number
________________
2014     21.00
2015     <10
2016     <10
2017     15.00

If I try to use 

Number of offers = IF([Number]
>9,
FORMAT([Number],""),"<10")

, the table appears to be correct, but because the numbers are formatted as text, they cannot be sorted accurately. For example, they appear as:

Animal   Number
________________
Fly       1001
Dog       109
Cat       1547
Rat       191

In summary: I want to format the numbers as a number, but without two decimal places.

I cannot change the Data Type to number, as the measure can contain a text string.

Unless you know of another way to resolve my original problem, I may post this again on the forum as another query.

Thank you for trying to answer my question; that is an interesting techique that I may use in the future!

[v-yulgu-msft]

Hi @TM_Visual,

What is the data typr of [Number] column in source table?

Actually, if it is text, we are unable to compare it with a numerica value [Number]>9. Also, when we create a new calculated column, it is not possible to return both numeric and text values in a single column. 

Number of offers = IF([Number]>9, [Number],"<10"

By the way, were you using Power BI desktop to generate reports?

Best regards,

Yuliana Gu