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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
LJLSR
New Member

Suppressing Visuals and data for too few records based on multiple slicers

Hi All

 

Am new to PowerBI so I hope this isn't too elementary. Searched the forums and help videos but not getting something I need exactly for consolidated data. Have had partial success with measures not managed a solution for full page. Below is sample set of a larger usecase.

 

For the below data/report, I have just one simple table. Based on all the slicers/filters on the page, I want to limit the data shown to only where more than 5 records available.

 

Data:

LJLSR_0-1714115828288.png

 

Report:

LJLSR_1-1714115879062.png

 

 

All the 3 visuals have averages (consolidated data). With the below data I need to limit to at least 5 responses, for example:

1) If no slicers selected. 

     - Visual 1 is ok, uses all records

     - Visual 2 should only show sweden data as Sweden has >= 5 records while other countries dont

     - Visual 3 should only show Tax and HR, as travel has < 5 records

2) Lets say US is filtered in one slicer

     - All 3 visuals should not show data as all visual components pull from < 5 records

3) If TAX and SWEDEN selected in different slicers

    - Again, all 3 visuals should not show data as all visual components pull from < 5 records 

 

If when data is suppressed and I can add some comment like 'Not enough data' this is an additional bonus.

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi  @LJLSR ,

 

Try formula like below:

age_ =
IF (
    SELECTEDVALUE ( Sheet[Country] ) = "US",
    BLANK (),
    IF (
        SELECTEDVALUE ( Sheet[Dept] ) & " "
            & SELECTEDVALUE ( Sheet[Country] ) = "TAX Sweden",
        BLANK (),
        AVERAGE ( [Age] )
    )
)
result1 =
VAR count_ =
    COUNTROWS ( FILTER ( Sheet, Sheet[Country] = MAX ( Sheet[Country] ) ) )
RETURN
    IF (
        SELECTEDVALUE ( Sheet[Country] ) = "US",
        BLANK (),
        IF (
            SELECTEDVALUE ( Sheet[Dept] ) & " "
                & SELECTEDVALUE ( Sheet[Country] ) = "TAX Sweden",
            BLANK (),
            IF ( ISBLANK ( SELECTEDVALUE ( Sheet[Dept] ) ) && count_ > 5, 1, 0 )
        )
    )
result2 =
VAR count_ =
    COUNTROWS ( FILTER ( Sheet, Sheet[Dept] = MAX ( Sheet[Dept] ) ) )
RETURN
    IF (
        SELECTEDVALUE ( Sheet[Country] ) = "US",
        BLANK (),
        IF (
            SELECTEDVALUE ( Sheet[Dept] ) & " "
                & SELECTEDVALUE ( Sheet[Country] ) = "TAX Sweden",
            BLANK (),
            IF ( count_ < 5, 0, 1 )
        )
    )

vkongfanfmsft_0-1714359381895.png

vkongfanfmsft_1-1714359389348.png

vkongfanfmsft_2-1714359407310.png

 

 

 

Best Regards,
Adamk Kong

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

1 REPLY 1
Anonymous
Not applicable

Hi  @LJLSR ,

 

Try formula like below:

age_ =
IF (
    SELECTEDVALUE ( Sheet[Country] ) = "US",
    BLANK (),
    IF (
        SELECTEDVALUE ( Sheet[Dept] ) & " "
            & SELECTEDVALUE ( Sheet[Country] ) = "TAX Sweden",
        BLANK (),
        AVERAGE ( [Age] )
    )
)
result1 =
VAR count_ =
    COUNTROWS ( FILTER ( Sheet, Sheet[Country] = MAX ( Sheet[Country] ) ) )
RETURN
    IF (
        SELECTEDVALUE ( Sheet[Country] ) = "US",
        BLANK (),
        IF (
            SELECTEDVALUE ( Sheet[Dept] ) & " "
                & SELECTEDVALUE ( Sheet[Country] ) = "TAX Sweden",
            BLANK (),
            IF ( ISBLANK ( SELECTEDVALUE ( Sheet[Dept] ) ) && count_ > 5, 1, 0 )
        )
    )
result2 =
VAR count_ =
    COUNTROWS ( FILTER ( Sheet, Sheet[Dept] = MAX ( Sheet[Dept] ) ) )
RETURN
    IF (
        SELECTEDVALUE ( Sheet[Country] ) = "US",
        BLANK (),
        IF (
            SELECTEDVALUE ( Sheet[Dept] ) & " "
                & SELECTEDVALUE ( Sheet[Country] ) = "TAX Sweden",
            BLANK (),
            IF ( count_ < 5, 0, 1 )
        )
    )

vkongfanfmsft_0-1714359381895.png

vkongfanfmsft_1-1714359389348.png

vkongfanfmsft_2-1714359407310.png

 

 

 

Best Regards,
Adamk Kong

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.