Forum Discussion
Page level and report level filter restriction
Okay, so the version I made with a cutdown set of data and imported instead of linked, doesn't seem to have the same strange behaviour. It does still only show "1" next to each filter though, so I guess that's showing "how many are in the Employees table" rather than "all the records with that value".
I'll try recreating the file from scratch against linked data, perhaps I broke something in my initial stumblings.
I'm not sure if I can attach a file here, it only seems to let me do photos and video, so here's a link to the file: https://drive.google.com/file/d/0B2pM_UFUxsNUSGtCNW9wTWFnX1E/view?usp=sharing
Continued ContributorCan't answer to the SQL Server side, but I know when Oracle is in Direct Query the relationships are omni-directional, not bi-directional. That would account for the difference you are seeing between the direct query and the imported data models.
Hmm, okay. I'll try it with a flattened SQL query instead of a PowerBI set of tables that it joins together automatically.
So I've tried this now using a query as a source instead of entire tables, but that causes another problem
My query is along the lines of
SELECT c.*, i.DisplayName AS Industry, e.Description AS EmployeeBand FROM Company c LEFT JOIN CompanyToIndustry ci ON (c.Id = ci.ZoomCompany_id) LEFT JOIN Industry i ON (ci.Industry_id = i.SearchInput) LEFT JOIN EmployeeBand e ON (c.EmployeeBand_id = e.Id)
And that gives me sort of what I want, as PowerBI is now just filtering a single big table. The problem I have is that my 'results' table that shows all company data will of course have repeated rows, as a Company can have multiple Industries, even if I don't want to show the Industry in the results table.
Is PowerBI really so unusable for linked data? I can't be expected to import everything into PowerBI? That's gigabytes of database, that's changing all the time.
As soon as I try to pull out even one dimension, I get the same old problem whereby filtering on "Industry" doesn't filter any other visualisations.
- v-qiuyu-msft
Community Support
Hi Cylindric,
1. And that gives me sort of what I want, as PowerBI is now just filtering a single big table. The problem I have is that my 'results' table that shows all company data will of course have repeated rows, as a Company can have multiple Industries, even if I don't want to show the Industry in the results table.
To comsume duplicate rows, you need to use DISTINCT keyword in your query, see: Eliminating Duplicates with DISTINCT.
2. Is PowerBI really so unusable for linked data? I can't be expected to import everything into PowerBI? That's gigabytes of database, that's changing all the time.
Power BI uses the relationships between the different tables and fields in the underlying data model to make items on a report page interact with each other. When we use multiple queries to get data, relationship among those result sets will be detected automatically. See: Relationship View in Power BI Desktop.
If you have any question, please feel free to ask.
Best Regards,
Qiuyun Yu