Forum Discussion

rolf1994's avatar
rolf1994
Helper II
9 years ago
Solved

Only show data when count is over 50

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.
Need Help
  • cs_skit's avatar
    cs_skit
    9 years ago

     

    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

v-yulgu-msft's avatar
v-yulgu-msft
Microsoft Employee
9 years ago

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's avatar
    rolf1994
    Helper II
    9 years ago

    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's avatar
      cs_skit
      Resolver IV
      9 years ago

       

      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's avatar
        rolf1994
        Helper II
        9 years ago

        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?