Filtering data in Power BI and Qlik Sense

Filtering data seems like such a basic concept.  That said the approach to filtering data in Power BI and Qlik Sense could not be more different.  The huge divide is the reason I decided to dedicate an entire post to filtering.

This article is part of a larger series of articles.  Use the buttons below to navigate to other sections.

Part One: Importing Data  Part Two: Data Models

Naming conventions: Slicers vs. Filters

Power BI use both terms and yet both do similar things.  Slicers are the physical objects that you place on a sheet to filter data.  Filters live in the sidebar and measures.

Qlik Sense uses the term Filter exclusively.  When you add a slicer, it’s not a slicer it’s a filter.

Also important.  Power BI refers to the document as a REPORT.  Qlik Sense refers to it as an APP.  So when I say app, I’m not talking mobile, I’m talking about the report in Qlik Sense.

Filter behaviors in Power BI and Qlik Sense

As I mentioned above the difference in the behaviours of filtering are massive.  I’m going to try to list them out.  If I’ve missed any that you know of, please hit me up in the comments.

1. Filtering across pages/sheets

In Qlik Sense when you apply a filter, it is throughout the entire app.  If you implement a filter for customer XYZ on page one of a report, that filter will affect all of the pages.

Power BI filters are page specific with one exception.  You can create a filter in the sidebar which will apply to the visual, the page or the entire report.  So taking the example above, if you apply a filter via a slicer to customer XYZ on page one, it will only be applied to page one.

Interestingly global filters are one of the most requested improvements in Power BI. You can vote for this feature here.

2. Filtering data from a table

Qlik Sense allows interactive filtering from within a table or pivot table.  Power BI does not permit dynamic filtering in tables and matrixes.  *** Note from Mike Mar 7 2017: Power BI have released a new preview matrix visual which allows cross-filtering of other visuals.

 

If you are a Power BI user, go vote for this feature here.

On the flip side Power BI allows static filtering from the sidebar.  Qlik Sense allows this using script in a Calculation condition field.

 

 

 

 

 

 

 

 

 

3. Filtering from visualisation objects

Power BI allows you to add unlimited filtering from slicer objects.  It however only allows one filter from a visualisation.  Qlik Sense allows an unlimited number of filters from visualisations.

You can vote for multiple cross-filtering in Power BI  here

4. The Qlik Sense filter bar

Qlik Sense has the magic black bar.  Ok, it’s not called that, but it is a very nice feature that allows the user to see all of the filters that are currently in use.  It also allows the user to remove all filters in one shot.  At this point, this is exclusive to Qlik.

5. Alternative states

Alternative states is an extension which allows users in Qlik Sense to put a fence around a group of visualisations in an app in which filters outside the fence do not apply.  This process currently requires a third party extension available here.  Power BI does not require such an extension as the filter sections in the sidebar allow additional control and filters do not carry over from page to page.

6. Filters from Search / Global Smart Search

Qlik Sense has the Global Smart Search tool. Power BI has search in Slicers and the natural language Q&A (in the app and online service only).

Again the behaviours here between the tools are very different.

The Global Smart Search tool is like Google for your app.  You can search for a combination of filters and then when you select those filters they are applied across the app.

Power BI natural language Q&A allows you to ask a question of your data and creates a visualisation as you type  It’s not the same thing, but it’s the closest version of global search in Power BI.

Power BI also have released recently search within slicers which work well, however, are limited only to the field the slicer is set up to filter.  The guys (with massive brains) at SQLBI have also developed a very nice third party visual slicer called Smart Filter which includes search as you type and keeps the selected filters shown.

 

7. Filter interactions between visualisations

Power BI includes the very handy Interactions button in the ribbon which allows the report developer to decide how they want each visualisation to interact with each other.  This graphical approach is very easy to use and works very well.

Qlik Sense handles these interactions in the script for measures for each visualisation or by using alternative states.  For me, this is an area that needs improvement.

8. Bookmarks in Qlik Sense

I almost forgot to include this one!   Once you create a group of filters that you are happy with, you can save that “state” in Qlik Sense so that you can return to it at any time.  Alternate states also work with bookmarks which improve their use considerably.  Bookmarks is a seriously great feature.

Is one better than the other?

I’m leaving this one up to the reader.  It could be argued that Qlik is the more mature product and that Global Smart Search is a game changer.  It could also be argued that the need for alternative states (which is a third party extension and has IMO a strange implementation) and hard coded interaction in Qlik Sense gives the upper hand to Power BI.

Another note: I have not added ideas for voting from the Qlik Sense community because I honestly find it a pain to navigate through.  With that said, It’s only fair that areas for improvement get included for both tools so if you know of one from above for Qlik Sense, let me know with the link address, and I’ll add it.

Have your say in the comments.  Which tool is the filter leader and why?