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

LJLSR — Fri, 26 Apr 2024 07:42:53 GMT

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:

Report:

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.

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

Anonymous — Mon, 29 Apr 2024 02:57:39 GMT

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 ) ) )

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.

topic Suppressing Visuals and data for too few records based on multiple slicers in DAX Commands and Tips

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

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