Forum Discussion
Page level and report level filter restriction
Hi folks. I'm just getting started with PowerBI Desktop, and I've hit a snag I can't quite work out.
I have a set of 4 SQL Server tables I've linked to, comprising company information, so there's "Company", "Revenue", "Employees" and "Industries". Company links to Revenue and Employees as a 1-M relationship, and to Industries as a M-M via a join table "CompanyIndustries".
I have a set of visualisations to show the breakdown of records as pie charts, and some Report level filters to drill down with.
My problem is that if I filter the report to only show companies with an industry of "Amusement Parks", only the "Industries" vis changes, the othres still show the global totals. The data table on the page seems to update correctly.
Here's an example - I'd expect the table Revenue Band and Employee Band chart to show a total of 26 records, not the grant-total unfiltered data.
Another somewhat odd symptom I'm seeing are the totals in the filter box. Employees and Industries always shows just "1", as if it's showing the distinct count from the child table, but Revenue Band is correctly showing the number of company records. That leads me to assume I've imported or defined my data in some weird way.
Any tips on how I can fix my amateur report?
10 Replies
- Greg_Deckler
Community Champion
Super tough to decipher, any chance you can share the PBIX file so that we can crack it open and see what is going on?
I probably can, it doesn't contain any actual data though, just links to a SQL Server. Can I PM you the file? I'd rather not publish it directly in public.
- Greg_Deckler
Oh, you're using direct query? Hmm, that's not really going to help much then I fear.
