Only show data when count is over 50

[rolf1994]

Hi,

Would it be possible to do the following:

- Only show data when count is over 50.

We want to apply this because of privacy reasons.

[v-yulgu-msft]

Hi @rolf1994,

Suppose there is such a table view:

The count for A is less than 50, the count for B is over 50, so, in new table, it should only show data rows where [Rowgroup]=B.

Create a calculated table referring below formula:

New table =
CALCULATETABLE (
    Test_export,
    FILTER (
        Test_export,
        CALCULATE (
            SUM ( Test_export[Value] ),
            ALLEXCEPT ( Test_export, Test_export[Rowgroup] )
        )
            > 50
    )
)

However, if your requirement is dynamically show data rows in visual based on whether total row number is over 50, this is not possible to achieve that currently. As currently, there is no such an option to control the visual visibility.

As current description is too general, please elaborate your requirement with sample data and desired output.

Regards,
Yuliana Gu

[rolf1994]

Hi @v-yulgu-msft,

Sample data:

I have the following table:

Id, UserId, Category
1, xxx1, A
2, xxx2, A
3, xxx3, A
4, xxx4, A
5, xxx5, A
6, xxx6, B
7, xxx7, B
8, xxx8, B

There are 5 different users in category 'A' and 3 different users in category 'B'. I only want my report to show data when distinctcount of UserId is greater than 4.

When no filters are applied i want to see 8 results (which is greater than 4) but when category 'B' is selected in a dropdown i want to see no results. I can create a sample pbix file if this is not clear.

[cs_skit · Accepted solution]

as for COUNTDISTINCT I often have my problem with that in that I need to create an additional table

Like you can solve it by doing this

Modeling - New Table:

DistinctCountTable = SUMMARIZE(UserData;UserData[Category];"TestDistinct";DISTINCTCOUNT(UserData[UserId]))

create Relation Category to Category

then

when you click Apply its filtered correctly

no idea how this can be done more beautifully I always end up creating new table in these cases

[rolf1994]

Thanks, this was already really helpfull.

What I want to do is not pre filter data for categories that contain a distinctcount lower than 4 but I only want my report to show data if total DistinctCount of UserId is greater than 4. So if DistinctCount for UserId for Category A, B, C and D is 20 I want to see all categories. If DistinctCount of Category 'B' is 3 and someone filters on Category 'B' I want the report display no data.

Would that be possible?

[cs_skit]

just create an additional SUM measure:

when I select B data disappears

[cs_skit]

Create a Measure that does the specific count you need 

Put it in Filter with > 50