Categorie op Actionable BI

Set up dependant slicers with Power BI or Analysis services (tabular)

One of the frequently asked questions by clients working with Power BI and/or analysis services (tabular) is how to get filters or slicers to be dependant on attributes that are not from the same table.

During the evolution of Power BI users got more and better options to set this up. This blog will describe the most common used scenario’s and what the best option is in my opinion.

The case

Consider the datamodel in figure 1. In this case their are 2 dimensions (Product and Store) that are related to a fact table containing Sales data. Both dimensions have a 1-N relationship to the fact table. Notice the arrows on the relationship, both are pointed towards the Sales table. This means that filters applied to both dimensions are pushed through to the Sales table. For example if one product is filtered only the sales for that product is shown. This is the desired results as we want to slice the sales data by describing attributes in the related dimensions.

Fig 1

Looking at the data in the datamodel below we can see that not all products are sold in all stores and that there also is a product that hasn’t been sold anywhere (Foldingbike).

Fig 2

On top of this datamodel a simple report can be created as shown in figure 3. This report page contains two charts that displays sales information by product and store and contains 2 slicers for both ProductName and StoreName.

Fig 3

When one store is selected all products remain visible in the product Slicer but as we can see this store does not sell all products.

Fig 4

For a better end-user experience only products that Bike Amsterdam sells should be visible. Before a solution is implemented it is important to know why this slicer still contains all products. The usual answer I get when I tell people it is because both slicers are not directly related, I am told they are related through the Sales table and that is totally true. The reason why a selected store does not filter the product table is because of how this relationship works. This brings us to the first option.

Option one: changing relationship behaviour


The default 1-N (from Product or Store to Sales with the arrow pointing towards Sales) relationship only pushes filters from the one side to the many side. This is because there is a direct relationship. Selecting one store should filter the sales table down to only that store. But if we want to filter the products after selecting one store, that store filter should be pushed to the products table that does not contain stores. So this store filter must be transformed into a product filter that contains all products that are sold for that one store.

The first and most common used option to get these dependant slicers is by changing the default relationship to a relationship that pushes the applied filters in both directions. This can be done in the relationship view as shown in figure 5.

Fig 5

After changing the Product – Sales relationship to “Both”, under the cross filter direction, the model behaviour changes as follows. When selecting Bike Amsterdam the Sales table is automatically filtered down to only this store. Now this filter is transformed into all products that are sold by Bike Amsterdam and this is pushed to the product table resulting in a situation where the product slicer only shows products that are sold by Bike Amsterdam (shown in figure 6).

Fig 6

This works as expected but this relationship setting should be used with caution. In a data model like this it is safe to use but what if this datamodel is expanded and contains more tables?

Consider the changed datamodel in figure 7. In a situation where selecting both product and store should be dependant on each other both relationships have their cross filter setting changed to both. So selecting a product filters the stores and the other way around.

Fig 7

In the StoreLayout the amount of Square feet per section per store is stored. After adding a new chart to the report we can compare the distribution of space per store (figure 8).

Fig 8

Per store we can see the distribution of space. Now imagine a Power BI report that contains two pages. One page containing sales information per product and store (Figure 3 & 4) and one page that contains the store layout (figure 8).

As a user you are analyzing the sales per product and especially the non-existing sales for the Foldingbike. Because you want to keep track of this product you decide to leave the filter and continue analysis on square feet on the next page.

The chart from figure 8 is now empty as shown in figure 9.

Fig 9

This is due to the fact that the filter direction of the Sales – Store relationship is set to both. And this is the reason that it should be used with caution. I’ll briefly explain.

The slicer on product is set to FoldingBike. The relationship from product to sales (1-N) filters the sales table to only sales on foldingbikes. Because there are none the filter context for the sales table contains no data. Because the relationship between sales and store is set to both the filter on the FoldingBike is transformed into a filter for all stores that sell this bike. Because there are no sales the store table is also filtered down to no records. So there is an empty result for sales and store. Now if we follow the relationship from store to StoreLayout we can see that the filter on the store table (no results) is pushed to the StoreLayout filter (default behaviour from 1-N). Figure 10 shows how relationships are cascaded by changing the cross filter direction. There is no relation between product and storelayout but due to this setting there actually is a relationship.

Fig 10

Option two: custom slicer visual

Another frequently used option to get dependancy on slicers is by using a custom slicer visual. The hierarchyslicer and Smart filter are two visuals that accomplish the desired result. Both work simular and only need the fields to be selected.

Figure 11 shows this slicer with the hierarchyslicer custom visual. In this custom visual the dependancy is shown as a hierarchy that can be collapsed and expanded. It’s important to know that the more dependancy between slicers the deeper the hierarchy gets.

Fig 11

Figure 12 shows this setup with the smart filter custom visual. Slicers are shown seperately but are contained in one visual. Selecting from each slicer is done from a list or dropdown. It has a lot of styling options but some are only in the pro (paid) version.

Fig 12

Option three: default slicers with filter

For a long time Power BI did not support settings filters on slicer visuals. This prevented report creators to base slicers on a subset of data. For example show all attributes besides the Unknown value. This also applies to the case in this blog where all Products should be shown that have sales.

not so long ago this inability to set filters on slicer visuals was fixed. This does not yet work for the new filter pane unfortunately but will probably be implemented soon.

It’s super easy to set up. By selecting the Product slicer look at the filter section as shown in figure 13. By default the attribute that the slicer is based on is shown (ProductName). We are now able to add more filters. This can be all different filter options (Standard, TopN, Relative,..) but also supports measures. By adding the Sum of Amount measure and setting the criteria to “is not blank”. This slicer now only shows ProductNames where the Sum of Amount is not blank.

Fig 13

Looking back at the report (figure 14) we can now see that after selecting Bike Brussel only the product’s that have sales for Bike Brussel remain in the slicer.

Fig 14

This action can be added to the StoreName slicer to get the same functionality in reverse (product filters stores). This third option allows report creators to get dependant slicers without making model changes.

Conclusion

With the ability to set up filters on slicers, report creators can create user friendly dimensions without the need for model changes. All options can be used to accomplish the same result but in my opinion option three is preffered due to it’s simplicity and also that there is no need for custom visuals (that might be disabled in your organisation).

Hidden Gem: Power BI mobile reports

I am not the biggest fan of Power BI dashboards. They do look amazing navigating via mobile phone and are a great way to combine insights into multiple reports from one view but in my opinion they lack certain functionality to be used as a default.

What would make Power BI dashboards great

There are some basic functionalities that would improve the usability for Power BI dashboards in a great way. I’ll briefly summarize my view on these changes.

  • Updatable pinned visuals
    You decide your new report is finished. You pin a visual to a new or existing dashboard and you add the visual to the mobile layout. In the end you notice a filter has been applied during the pin-action. This requires the visual to be pinned to the dashboard and mobile view again. And I don’t mention the resizing and renaming in the dashboard. Change in colours, labels, set slicers etc require pinned items to be re-pinned. This makes the Power BI dashboard way to solid.
  • Global slicers
    Looking at the Power BI dashboard it could be that several visuals have a different granularity in for example years that are shown. To create a single glass to interpretate the data on the same granularity it would be an amazing addition to have something like global slicers that work over reports. So a global slicer for “Year” would filter all visuals to the same period. A good starting point would be to have the Edit interaction option from Power BI desktop
  • Multi-pin in mobile view
    A mobile dashboard view can be styled in a great way by using titles and images. If you want to add images to this mobile view you need to have them in your dashboard and even if you want to use the same image, like a logo, multiple times you need to have that image in your dashboard mulitple times. An option to multi-pin the same visual would be a great addition to the mobile view but also keep the original dashboard cleaner
  • More flexilibity in dashboard layout
    The Power BI dashboard can be improved with having more flexilibity in the dashboard tile layout. Being able to change the width and height without restrictions, default settings for titles, subtitles, font etc and last but not least the ability to add content items that are not shown like images that should only be visible in the mobile view

Eventhough I see a lot of improvements I still fancy the easy and intuitive way a dashboard can be created in very little time. In my day to day job I see a lot of businesses using a default approach to Power BI content. This starts with Power BI desktop, Power BI service (report), Power BI service (dashboard) and finally sharing it with collegues. Probably this has lots to do with dashboard sharing being the only option in the beginnen for Power BI.

User navigation

In a situation where a dashboard is shared with an end-user this person can directly go to powerbi.com to navigate to the shared dashboard. After selecting a visual the user is directed to the underlying report for further analysis. This works fine and the user can get to the report fairly quickly.

If the user navigates to the dashboard via the Power BI mobile App this experience is slightly different. I really fancy this dashboard view because it has lots of space with enough room for clear visuals.

Fig 1

This dashboard view is read-only and after selecting one visual it will open to full screen. The user can see the data labels here or choose to annotate and share his thoughts (Fig 2).

Fig 2

The interactivity on this visual is limited to this visual only. If this users want’s to interact with other visuals it can navigate to the underlying report (Fig 3) by selecting the little barchart in the top right corner.

Fig 3

Eventhough phones are getting bigger and bigger still this report view is not very user friendly. Especially when there are lots of visuals and slicers on the canvas.

Power BI Phone layout

From within Power BI desktop each report page can have a mobile phone layout. This can be selected from the View tab on the ribbon.

Fig 4

By dragging & dropping items from the right panel the mobile view can be created. Yes it’s true that multi-pin should be a great addition here also!

After saving and publishing the report this mobile view can now be accessed by selecting the litle bar chart on the top right from Figure 2.

Fig 5

Now also the report is readable on mobile devices and the user is able to interact between visuals as shown in Figure 6. This improves user experience greatly and data analysis on their mobile phone.

Fig 6

Does this replace the Power BI dashboard?

The Power BI dashboard still has some great features like subscriptions, alerts and multi-report visuals but if you don’t use these this mobile view could replace the Power BI dashboard. You can also directly share a Power BI report without having to create a Power BI dashboard.

Conclusion

I see lots of organizations using the Power BI mobile app without configuring their reports for mobile consumption. It takes a little bit more time to create a mobile view for every report page but it enhances the user experience on mobile greatly!

Dynamic titles in Power BI

Since the April release of Power BI desktop it is possible to use dynamic titles in your visuals. This can be super handy when there is a need to explain more than just a static title. For example a static title would be “Sales over time” and a dynamic variant could be “Sales between jan 2019 and july 2019”.

To get to this option select a visual, navigate to the title menu and select “Conditional formatting” from the three dots dropdown.

In the popup that appears you can select an attribute. This can be either a column or a measure but it must be from a Text data type. If you select a column you must specify the summarization because this dynamic title can only handle a single value. To get started it is important to know what context is applied to the visual and how we can access attributes that will create our dynamic title.

Evaluation context

In DAX there is always a evaluation context. This is the context that affect a DAX expression (calculated column, measure) when it is executed. Most important is that you have an understanding about filter context, row context and context transition. You’ll start exploring these kinds of contexts whenever you build a measure that does not return the value you would expect. To explain these contexts I prefer referring to the best explanation that can be found here. In this blog I will refer mainly to the evaluation and filter context.

Getting started

First lets start with a simple dataset and some visuals.

Fig 1
Fig 2

We’ve got some data for several months and two products. The title is set to “Sales over time” and this should have an indication from the period that data is shown, lets say “Sales from 1-1-2019 until 1-6-2019”.

To do this we need to be able to grab both dates from the dataset. This can be done if we know what the evaluation context for this visual is.
The evaluation context is the environment in which a formula is evaluated and this context changes through slicers, filters, rows and columns in visuals. For this visual there is a filter context at each data point (every date) but if we look at the chart itself we could refer to the title as being the “total” value. There is no filter set by any slicer, filter, row or column value. If you look back at the data table (Fig 1) you can see that there are no rows excluded by matching the values in the visual.

Building the dynamic title

Create a new measure from the field list. To create the dynamic title the below DAX formula would do the trick.

DynamicTitle = 
VAR FirstDt =
    MIN ( Data[Date] )
VAR LastDt =
    MAX ( Data[Date] )
RETURN
    "Value from " & FirstDt & " until " & LastDt

Because dates are chronologically ordered we can grab the first date by selecting the minimal value and the max date by selecting the maximum value. I prefer to use variables because this enhances readability and has soem benefits in only calculating once.

The final line builds the dynamic title resuling in the desired outcome.

We could enhance the title in combination with the Product slicer. A slicer changes the filter context so whenever one product is selected we could use a MAX() MIN() FIRST() LAST() or any other summarization to select this product the same way as with the date but we could also concatenate the selected products by using SELECTEDVALUE()

Title = 
VAR FirstDt =
    MIN ( Data[Date] )
VAR LastDt =
    MAX ( Data[Date] )
VAR Product =
    SELECTEDVALUE (
        Data[Product];
        CONCATENATEX ( VALUES ( Data[Product] ); Data[Product]; "," )
    )
RETURN
"Value from " & FirstDt & " to " & LastDt & " for product (" & Product & ")"
Fig3 The new title with product A selected
Fig 4 With both products selected

Conclusion

Dynamic titles are a great way to give more information about what is displayed in a visual. Whenever you start with dynamic titles you should consider when this title becomes a paragraph that creates information overload. The basic idea should be that it helps understanding the visual.