Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I'm fairly new to Power BI. I have a table with
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.
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.
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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |