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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jrosales
Regular Visitor

Filter Measure with its Aggregated Value with multiple slicers

I'm fairly new to Power BI. I have a table with

  1. Student
  2. Class Year
  3. Major
  4. Financial Aid received (yes/no)
  5. First Generation Status (whether parents attended college)
  6. various outcomes like completion of Math requirement, course pass rate, GPA

I am creating a report in Power BI desktop which has a clustered column chart of student count by Class Year, A Guage showing the Course Pass Rate, A Table showing the GPA and the student Count... basically, multiple data outcomes on the same page.

I am filtering using slicers for Major, First Generation, and Financial Aid recipient (There may be some other slicers included but for now this is what they want to see). I want to apply a filter to only display data when the student count is >10. I tried creating a measure =Count(Table1[Student]) and entering it as a visual level filter. I was able to get that to work for the clustered column chart but none of the others. It also would not work if I changed the other charts to clustered columns. I could not select the drop down for the greater than...). I could not add it as a report or page level filter, either. I saw some suggestions for creating a slicer, but I don't want the end user to have control over this filter.

I saw a suggestion about creating a table using something like: 

     Table2 = SUMMARIZECOLUMNS ( Table1[Major], "Enrollment", [StudentCount])

 

but how can I do this type of summary if I have to be able to filter on multiple variables?

We want counts >10 to protect student identity as much as we can.

2 REPLIES 2
v-yuta-msft
Community Support
Community Support

Hi jrosales,

 

You can create a calculate column use SUMMARIZECOLUMNSand add a filter to it like pattern below:

 

Table2 =
SUMMARIZECOLUMNS (
    Table1[Major],
    "Enrollment", DISTINCTCOUNT ( Table1[StudentCount] ) > 10
)

Or you can create a measure to achieve count>10 based on multiple slicers using DAX like this pattern:

 

Result =
CALCULATE ( DISTINCTCOUNT ( Table1[StudentCount] ), ALLSELECTED ( Table1 ) )

Regards,

Jimmy Tao

Thank you for the advice, Jimmy. It didn't quite work. When I tried the first suggestion, I received "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value." when I tried to add a column and I received a value of True/False when I tried to add it as a new table.

 

The second option seemed to work for one clustered column chart but for no other charts, including other clustered column charts.

 

I have a table like this.  I want to be able to include the highlighed columns as slicers.

StudentClassYearMajorFinancialAidFirstGenGPAOutcomeAOutcomeBOutcomeC
AlfredJuniorSTEM102111
BettySeniorARTS113111
CharlieSeniorARTS114111
DanSeniorSTEM113010
EricJuniorSTEM112010
FrancisJuniorSTEM113011
GregJuniorARTS013101
HollyJuniorARTS012001
IwaJuniorSTEM113111
JoseJuniorSTEM114100

 

If it is completely disaggregated, the totals would look like:

ClassYearMajorFinancialAidFirstGenCount of StudentAverage of OutcomeA
JuniorARTS0120.5
 JuniorSTEM1011
 Junior STEM 1140.5
SeniorARTS1121
 SeniorSTEM1110
Grand Total  100.6

 

Assuming I only want data where student counts are >=2, it should only display data from rows 1, 3, and 4.  I want to be able to have different charts or tables for the different outcomes.  However, it should show everything if there is no slicers selected because there will be enough students.

 

Thanks again.

Josh

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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