Forum Discussion

Cylindric's avatar
Cylindric
Regular Visitor
10 years ago

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...
Need Help
Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
10 years ago

Oh, you're using direct query? Hmm, that's not really going to help much then I fear.

Cylindric's avatar
Cylindric
Regular Visitor
10 years ago

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

 

  • Cylindric's avatar
    Cylindric
    Regular Visitor
    10 years ago

    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

     

    • Cylindric's avatar
      Cylindric
      Regular Visitor
      10 years ago

      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.

       

    • dramus's avatar
      dramus
      Icon for Continued Contributor rankContinued Contributor
      10 years ago

      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's avatar
        Cylindric
        Regular Visitor
        10 years ago

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