Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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.
Solved! Go to Solution.
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.
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.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
11 | |
9 | |
7 | |
6 | |
5 |
User | Count |
---|---|
20 | |
11 | |
10 | |
9 | |
6 |