Sunday, July 24, 2016

Three Creative Ways to Use Microsoft Power BI Filters

Data

As many of you explore Microsoft Power BI, you likely find the use of filters very helpful. As you probably are aware, filters allow you to control the data that is displayed at the visual, page and report levels. In this blog post we explore three creative ways you can make use of filters to solve some common end user requests.

Global Filters on Dashboards

One of the most popular requests I get from users is to be able to set a global filter such as date or company and have the entire dashboard reflect the selected filter value. A straight forward way to support this request is to change the page size of the report page to Custom and set the Height property to a size that would encapsulate all of the visuals of your dashboard. For example, we have changed the standard Height from 720 to 1200 pixels as shown below.

clip_image002[4]

Place the requested filters at the top of the page (as shown below) and your visuals below it and then use the “Pin Live Page” option to pin your report to the appropriate dashboard.

image

Search Across Columns

One interesting request we recently received from a customer was the ability to search for keywords across multiple columns. Power BI has recently enhanced the standard Slicer to allow you to search the values in the slicer use a text based contains search. However, this only allows you to search on one column. A way to provide for searching across multiple columns is as follows:

1. Use Edit Queries to duplicate the columns you wish to combine in keyword search

2. Merge the duplicated columns together using a space as a delimiter

3. Rename this new column Keyword Search

4. Close and apply the queries

5. Add the new column, Keyword Search to the applicable report pages as a page filter as shown below

6. Once the user enters a value for the search, the report page will reflect only the results matching the keyword choice(s).

clip_image006[4]

Rolling 12 months of Data

When delivering trend graphs, an often requested feature is to display a rolling 12 months of data. In order to accomplish this, we will need to add a measure and a calculated column to our Date table as follows:

Measure

Today = DATE(YEAR(NOW()),MONTH(NOW()),DAY(NOW()))

Calculated Column

Last 12 Months = IF(AND('Calendar'[Date]>=[Today]-365, 'Calendar'[Date]<=[Today]),1,0)

If you would like to change the rolling time period, simply change the value above from 365 to the desired number of days.

Once you have created these, apply the Last 12 Months column as a Page or Visual filter and select only values of 1 as shown below.

clip_image008[4]

I hope that these tips will help you get the most out of your Power BI experience.