Forum Discussion
Slicer not slicing
OK, this one has me scratching my head a bit. I have some Ohio school district data that my sister sent me. Schools are basically rated based upon student performance on standardized tests and such. I thought it would be interesting to calculate the Mean (Average) score for all schools, the standard deviation of their scores and then calculate each schools's number of standard deviation's from that mean. Then, I could have a slicer to break the schools down by category (rural, urban, etc.). The thought here was that I could find outliers in the data so that my sister could focus on schools that overperformed for their category and perhaps that would lead to finding out what those schools are doing differently.
So, everything went well up until I put a slicer on the school's category and then I got some really weird results.
So, here is some sample data:
DISTRICT
| DISTRICT IRN | DISTRICT NAME | OVERALL SCORE |
| 442 | Manchester Local | 2.75 |
| 43489 | Akron City | 1.31 |
| 43497 | Alliance City | 1.63 |
| 43505 | Ashland City | 2.63 |
| 43513 | Ashtabula Area City | 1.94 |
| 43521 | Athens City | 2.69 |
| 43539 | Barberton City | 1.69 |
| 43547 | Bay Village City | 4.19 |
| 43554 | Beachwood City | 3.63 |
| 43562 | Bedford City | 1.69 |
| 43570 | Bellaire Local | 1.69 |
| 43588 | Bellefontaine City | 3.25 |
| 43596 | Bellevue City | 2.94 |
| 43604 | Belpre City | 2.19 |
| 43612 | Berea City | 1.94 |
| 43620 | Bexley City | 2.88 |
| 43638 | Bowling Green City | 3.25 |
| 43646 | Brecksville-Broadview Heights City | 4.19 |
| 43653 | Brooklyn City | 2.69 |
| 43661 | Brunswick City | 3.5 |
| 43679 | Bryan City | 2.94 |
| 43687 | Bucyrus City | 1.81 |
| 43695 | Cambridge City | 2.75 |
| 43703 | Campbell City | 1.75 |
| 43711 | Canton City | 1.56 |
TYPOLOGY BY DISTRICT
| IRN | District Name | 2013 Typology |
| 442 | Manchester Local (Adams) | 1 |
| 43489 | Akron City | 8 |
| 43497 | Alliance City | 7 |
| 43505 | Ashland City | 4 |
| 43513 | Ashtabula Area City | 4 |
| 43521 | Athens City | 5 |
| 43539 | Barberton City | 7 |
| 43547 | Bay Village City | 6 |
| 43554 | Beachwood City | 6 |
| 43562 | Bedford City | 7 |
| 43570 | Bellaire Local | 4 |
| 43588 | Bellefontaine City | 4 |
| 43596 | Bellevue City | 1 |
| 43604 | Belpre City | 4 |
| 43612 | Berea City | 7 |
| 43620 | Bexley City | 6 |
| 43638 | Bowling Green City | 5 |
| 43646 | Brecksville-Broadview Heights City | 6 |
| 43653 | Brooklyn City | 7 |
| 43661 | Brunswick City | 5 |
| 43679 | Bryan City | 4 |
| 43687 | Bucyrus City | 4 |
| 43695 | Cambridge City | 4 |
| 43703 | Campbell City | 7 |
| 43711 | Canton City | 8 |
Topologies
| 2013 Typology Code | Major Grouping |
| 0 | Island |
| 1 | Rural |
| 2 | Rural |
| 3 | Small Town |
| 4 | Small Town |
| 5 | Suburban |
| 6 | Suburban |
| 7 | Urban |
| 8 | Urban |
I built these measures (in DISTRICT):
Overall Score District Mean = AVERAGE(DISTRICT[OVERALL SCORE]) Overall Score District StdDev = STDEV.P(DISTRICT[OVERALL SCORE])
And then this custom column in DISTRICT:
OVERALL SCORE STDDEV FROM MEAN = (DISTRICT[OVERALL SCORE] - CALCULATE([Overall Score District Mean],ALL(DISTRICT))) / CALCULATE([Overall Score District StdDev],ALL(DISTRICT))
Here are the relationships
And here are some visuals, you can see I have a slicer and some tables:
Now for the weirdness. I click on Island and both of my two bottom tables are filtered correctly, but the table in the upper right is not!
Now when I click on Rural in the slicer, I get more, different weirdness...
I feel like I am missing something obvious but I can't seem to find it. I think it must have something to do with my measures I have to imagine. Maybe the ALL is doing something bizarre. But, in any case, this is certainly not what I would have expected... Anyone have any thoughts?
6 Replies
Hi Greg_Deckler,
I replicated what you did with the data you provided and it worked fine for me. Perhaps if I had the full dataset I can test it further. I remember Power BI Desktop returning a lot of random blanks when there are a large number of rows. But this was fixed a few months back. Perhaps this is another issue nobody has picked up on.
As you can see, the image below, it is working fine. I did make a few changes to the data hence why 'Dan City' is showing.
djnww - Really appreciate you taking a look at this. At least I know I'm not crazy and that this *should* work. I have not had a chance to recreate with this smaller data set (even weirder in some ways that it works). I posted the PBIX file that I am working with if you or someone wouldn't mind seeing if I did something stupid or can confirm that it simply does not work with the larger data set. If that is the case, I will post it as an issue.
Link to PBIX file:
https://1drv.ms/u/s!ApodhCa32_44haZPaYqNcB-AlNOFeQ
djnww - I finally found some time to replicate what you did, and it works beautifully, exactly as intended. So, now I am scratching my head on why in the world the original model does not work at all. It seems built exactly the same. Any chance you are able to look at the PBIX file I posted and see if you can find anything obvious before I open an Issue on this? I'm really perplexed now.
BTW, I posted the "small" data model built off the sample data I posted here:
https://1drv.ms/u/s!ApodhCa32_44hat7gFz6H4Hkyb0HfA
This is the one that works.
