Forum Discussion
Changing low counts to mask/remove potential identification.
Resolver ITo display the student count as "<10" in Power BI Desktop when the count is less than 10, you can use the following formula in a measure:
```
Student Count =
IF(
COUNTROWS('Sheet1') < 10,
"<10",
FORMAT(COUNTROWS('Sheet1'), "0")
)
```
This formula first checks if the count of rows in the table is less than 10. If so, it returns "<10". If the count is 10 or greater, it formats the count as an integer with zero decimal places using the `FORMAT` function.
To ensure that the "<10" value is sorted correctly in your table, you can set the sort order of the Student Count column to be based on the values rather than the alphabetical order. To do this, select the Student Count column in the table, click on the "Modeling" tab in the ribbon, and select "Sort by Column" from the "Column tools" section. Then choose "Student Count" from the list of available columns.
Alternatively, you can create a separate column in your table that contains a numeric version of the Student Count measure. You can then sort the table by this column while still displaying the "<10" values in the Student Count column. Here's an example formula for the numeric version of the Student Count measure:
```
Student Count Numeric =
IF(
COUNTROWS('Sheet1') < 10,
BLANK(),
COUNTROWS('Sheet1')
)
```
This formula returns a blank value when the count is less than 10, and the actual count when it's 10 or greater. You can then set the sort order of the table based on this column while still displaying the "<10" values in the Student Count column.
I think you misunderstood me.
The tables I have created are a visual, not the "Data View" tables. Unless you are suggesting, I make a new Data View table with the measure? If so, I cannot because the slicing within the Dashboard would become void.
The tables in my OP result from the visual I created - Count and Student Count (measure) - for my Dashboard from the Data View table called "Sheet1". "Sheet1" is in the Data View. It is the Excel doc I am pulling my information from. The Excel doc has three years of data and roughly 200,000 rows. Each row is an individual student. The measure I need to use is DISTINCTCOUNT so that I do not count a student twice - if they are listed twice.
When reporting, I cannot display a count under ten and must change it to "<10". Whether the count is greater than or less than ten depends on the slicers and count - which my measures already allow. However, when sorting, any value less than ten is considered the highest number.