Filter Measure with its Aggregated Value with multiple slicers

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

Student
Class Year
Major
Financial Aid received (yes/no)
First Generation Status (whether parents attended college)
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.

Need Help

2 Replies

v-yuta-msft

Community Support

8 years ago

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

jrosales

Regular Visitor

8 years ago

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.

Student	ClassYear	Major	FinancialAid	FirstGen	GPA	OutcomeA	OutcomeB	OutcomeC
Alfred	Junior	STEM	1	0	2	1	1	1
Betty	Senior	ARTS	1	1	3	1	1	1
Charlie	Senior	ARTS	1	1	4	1	1	1
Dan	Senior	STEM	1	1	3	0	1	0
Eric	Junior	STEM	1	1	2	0	1	0
Francis	Junior	STEM	1	1	3	0	1	1
Greg	Junior	ARTS	0	1	3	1	0	1
Holly	Junior	ARTS	0	1	2	0	0	1
Iwa	Junior	STEM	1	1	3	1	1	1
Jose	Junior	STEM	1	1	4	1	0	0

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

ClassYear	Major	FinancialAid	FirstGen	Count of Student	Average of OutcomeA
Junior	ARTS	0	1	2	0.5
Junior	STEM	1	0	1	1
Junior	STEM	1	1	4	0.5
Senior	ARTS	1	1	2	1
Senior	STEM	1	1	1	0
Grand Total			10	0.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

Forum Discussion

Filter Measure with its Aggregated Value with multiple slicers