Page level and report level filter restriction

[Cylindric]

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?

[Greg_Deckler]

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?

[Cylindric]

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.

[Cylindric]

I'll try and mock something up with cached data, that might make it easier to test.

[Cylindric]

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

[dramus]

Can'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.

[Cylindric]

Hmm, okay. I'll try it with a flattened SQL query instead of a PowerBI set of tables that it joins together automatically.

[Cylindric]

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]

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

[Cylindric]

Hmm, I think I might see what it's doing.

Let's say:

• the total number of companies with the employee band of "10-20" is 86k.
• the total number of companies with the industry of "construction" is 27k.
• the total number of companies with the revenue band of "£1M to £5M" is 254k.
• the total number of companies is 400k.

When I report-level-filter on Employee Band and select "10-20", all four visualisations change to what I'd expect:

• 86k in a single pie-slice for employees.
• 8k in the "construction" bar for industries.
• 86k total in the revenue pie, in two slices.
• 86k records in the table.

If I then filter by "construction" is where things go weird:

• 86k in a single pie-slice for employees. I'd expect this to be 8k.
• 8k in a single "construction" bar for industries (this is okay)/
• 86k total in the revenue pie, in two slices. I'd expect this to also be a total of 8k/
• 86k records in the table, this should also only be 8k records.

Are the report level filters not accumulative? How do I make them so they are?

If I do the same thing in the report I made using locally imported data, it seems to work as I'd expect.