The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
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.
User | Count |
---|---|
28 | |
11 | |
8 | |
6 | |
5 |
User | Count |
---|---|
35 | |
14 | |
12 | |
9 | |
7 |