skip to Main Content

ChicletsAdded

Slicers are Empowering!

There have been several blogs and articles written on dynamic measures and how to implement them in Power BI Desktop or Power Pivot using “Harvester” measures and disconnected slicers. See here & here.  These techniques are popular with report users because they let report users choose the items they want to see and take control of the report.  When one your customers ask for a report to be sliced a certain way, you don’t build them a new report, you add a slicer to a current report and teach them how to “click the button.” Ahh, empowerment!

Three Flavors of Disconnected Slicer

So for us report builders disconnected slicers enable us to empower our users, and we’ve been doing this for years. With the emergence of Power BI, disconnected slicers are more important than ever.  The Power BI canvas is smaller than the Power Pivot canvas, and slicers of all kinds (especially the disconnected kind) let us turn one report into many reports.  Dynamic measures is one type of disconnected slicer technique. The other two techniques are dynamic parameters (made much easier with the release of variables in Power BI) and dynamic attributes.  This last technique, dynamic attributes, hasn’t gotten much attention, so let’s fix that!

What are Dynamic Attributes?

How often have you heard something like this. “I know I asked you to slice this Cost report by region, thank you for doing that. Now would you mind creating a version that shows the exact same Cost data but that’s sliced by industry.” For those of us in the reporting world, we have this conversation all the time. Without dynamic attributes, you would likely end up creating a new version of the report every time you get a question like this – maybe you end up with Cost by Region, Cost by Industry and Cost by Vertical reports. In Power BI this means creating three separate report pages, or at the very least three separate visuals.

Wouldn’t it be nice to have a button that says “What would you like to slice on?” and the report user can choose “Industry, Region, or Vertical.” When Austin and I first discussed this, we agreed it would be great – we just had no idea how to do it. 🙂 So let’s agree that creating 3 separate report pages or 3 separate visuals takes up too much real estate in Power BI and that 1 visual with a “Slice By” button makes more sense. Imagine if you could have one visualization and allow the user to dynamically select the attribute they want to visualize. This is dynamic attributes.

Example Data

Let’s start by looking at our example data. Below is a data table that contains the important information for our report. The attributes you want the user to select dynamically are Region, Industry and Vertical. The values we have are Amount and Cost columns, and these will end up being measures.  If you want to follow along and do this yourself, download the sample data here and use Get Data from Power BI Desktop to import into your model.

Sample data table

Sample data

The Old Way

In a typical approach, you start by pulling the data into your model, creating a couple of simple measures called Total Invoice Amount and Total Cost. For the sake of time, let’s assume you know how to create simple measures such as this. You then quickly slap three visualizations on the page to analyze the Total Invoice Amount by the various attributes. This works but isn’t very exciting or dynamic.

Takes up too much room

The old way of doing it

The Beginning of Your New Dynamic Life!

Here’s a piece of wisdom you can keep with you. If you want to put something in a slicer it needs to be in a column. For example, if you want to be able to choose between Region, Industry, and Vertical then you need ONE column that contains those THREE values. If you’ve worked with disconnected slicers before then you’re used to creating a table with all of your options.

To begin the process of making these attributes dynamic, we need to do something with our data model. A middle table, sometimes called a bridge table is needed. A bridge table is generally used when a many-to-many relationship is needed. This is what our bridge table will end up looking like.

Bridge Table

Bridge table

Let’s Build A Bridge

In the above image. each invoice is represented by three rows because we have three attributes we want to dynamically select. If you had 5 then you would have 5 rows for each invoice. The total rows in our bridge table will be the number of rows in our data table multiplied by the number of attributes you want to dynamically select.

Prior to Power Query and Query Editor, our bridge table would have been developed with a set of SQL statements implemented as part of a data process or ETL operation. Now, with Power Query and just a few clicks, you can create a version of a bridge table from your data table. Let’s walk through this one step at a time.

Step One:

Pull in your data table via Get Data and click on Edit Queries to open the Query Editor.

Picture3

Edit Queries

 

Step Two:

Make a copy of your data table. You do this by right clicking on the data table and selecting Duplicate.

Picture4

Copy data table

This will make a copy of the Data table called Data (2). You will want to rename this new table and call it Bridge.

Step Three:

You will need to use the Unpivot function. This is a powerful function that turns multiple column headers into a single column but in rows and stores their values in another column. To do this, highlight all three columns (Region, Industry and Vertical) by holding down the CTRL key and selecting each column. Then, with these three columns selected, click on the Unpivot Columns button on the Transform ribbon option. Notice how your table is now taller and thinner.  The default name for the unpivoted columns is Attribute and Value.

UnPivot

Unpivot

 

Step Four:

Remove the Amount and Cost columns by going to Home and selecting Remove Columns.

Remove Columns

Remove columns

 

You now have an easily created bridge table thanks to the genius of the Query Editor.  Click the Close and Apply button and now you are ready to proceed to creating a Slicer attributes table.

The Fun Starts Now: Create a Slicer Attribute Table

You need to create a table that will contain the attribute values that you want to be dynamic. This table will be used as the source to a slicer that will eventually be put on your report. Let’s start by making a copy of the Bridge table you just created. For the sake of time, do the same thing you did in Step 1 when creating the Bridge table except this time Duplicate the Bridge table and rename it to AttributeSlicer.

Next, we simply need to remove the Invoice and Value columns by selecting Remove Columns with both columns selected. Since this table will be a lookup table and used to source a slicer on your report, you only want distinct values in this table so another useful Query Editor function can be used called Remove Duplicates. This function can be found under Remove Rows on the ribbon. Once you do this, the result of your AttributeSlicer table should contain only distinct values for each attribute that you want to dynamically select.

Picture2

Remove Duplicates

 

Click the Close & Apply button and let’s move on to the next part of this process.

Relationships

Relationships are the key to an enjoyable life.  They are also extremely important when working with Power BI.  For things to work the way you want, relationships are needed between the tables you have created.

Create a relationship between your data table and the bridge table on the Invoice column. Create another relationship between the AttributeSlicer table and the Bridge table on the Attribute column. This is how it should look.

Relationships

Relationships are key

 

Time To Build The Report

Start by creating a slicer and use Attribute as the value from the AttributeSlicer table. Then, create a clustered bar chart with the Value attribute from your Bridge table on the Axis and the Amount column from your data table as the values. Turn on and format your data labels and it should look something like this.

First version of report

First version of report

 

Now, select an attribute from your slicer and see what happens to the visualization. It is filtered to only show the values for the attribute you selected. Great! But wait, there is a problem. All the values are the same!!! This is because the AttributeSlicer table has a relationship between the Bridge table but the Bridge tables relationship to the Data table, where the Amount is coming from, is only one-way.  If you change the cross-filter direction to be Both then you get the correct amounts. Double-click this relationship and change the Cross Filter Direction to Both and then notice the bi-directional arrow on the relationship diagram as seen below.

Picture3

Cross filter direction

Picture4

Cross filter diagram

The report now looks and acts like you would expect and you can dynamically select which attribute you want to visualize.  Go ahead, play with it and be proud of yourself.

Second version of report

Second version of report

Let’s Make It Better!

This is good progress but is still not good enough. Let’s make it better. First, let’s create measures for the two columns that we want to aggregate, Amount and Cost. Call them Total Invoice Amount and Total Cost using the following two DAX formulas:

Total Invoice Amount = SUM(Data[Amount])

Total Cost = SUM(Data[Cost])

Dynamic Measures

Now let’s create a MeasureSlicer table so we can make our measures dynamically selectable as well.

Click on the Enter Data button on the Home ribbon bar. A table editor will appear where you can add data. For now, let’s add Amount and Cost to the rows and label that column Measures. Add a second column and call it SelectedPosition and put the values 1 and 2 accordingly. Finally, name your table MeasureSlicer . Now click Load to add it to our model.

Picture5

Enter data

Once in your model, it is a good idea to hide the SelectedPosition column on your new MeasureSlicer table.

Picture6

Hide columns

 

Ok, let’s add a slicer so the user can dynamically select the measure they want to visualize. To do this, 1) select Measures in the MeasureSlicer table so that it is added as a new visualization to your report. 2) Then, change the visualization type to Slicer.

Picture7

Add measure slicer

It Doesn’t Work!!!

CAUTION! It won’t do anything just yet because we haven’t created the secret sauce, the Magic Formulas.

So, to make this solution totally rock, let’s create one more table to hold our “Magic” formulas. These three formulas or measures will allow us to toggle between the two measures and dynamically adjust the title of the visualization based on the selected attribute and measure.

Just like before, click Enter Data on the Home ribbon, give this table a name called Calculations and click Load. That’s it. You don’t even have to enter anything. Now, for the work. You will create three measures by right clicking on your new Calculations table and selecting New Measure. You will find the code below (bold) for these three measures with a brief explanation of each.

SelectedIndex = Min(MeasureSlicer[SelectedPosition])

The SelectedIndex measure’s job is to get the SelectedPosition value for the measure that is filtered via the Measures slicer you just added. In this case, the SelectedPosition value will be either 1 or 2.

Magical Formula = IF(HASONEVALUE(AttributeSlicer[Attribute]) && HASONEVALUE(MeasureSlicer[Measures]), Switch([SelectedIndex],1,[Total Invoice Amount],2,[Total Cost]))

The Magic Formula’s job is to ensure that only one attribute and one measure is selected and if so, use the Switch command to set, hold or “Harvest” as Rob Collie would say, the name of the measure that is to be visualized. Go ahead and add the Magic Formula to the Value area of the visualization. Now, you can dynamically select either Amount or Cost in the slicer and watch the measure dynamically change on the visualization.

Picture9

Magic Formulas

 

Dynamic Title = IF(HASONEVALUE(MeasureSlicer[Measures]) && HASONEVALUE(AttributeSlicer[Attribute]),VALUES(MeasureSlicer[Measures]) & ” by ” & VALUES(AttributeSlicer[Attribute]), “Pick One Measure & One Attribute”)

The Dynamic Title’s job is to again ensure that only one measure and one attribute is selected and then dynamically build a measure based on the selected Measure and Attribute. Add this measure to a Card visualization and place it above the chart so it will read something like “Amount by Region” or “Cost by Industry” and it will be a title that lets the user know what they are looking at in the visualization.  If none or more than one attribute or measure is selected, the measure will read “Pick One Measure & One Attribute”.

Picture10

Dynamic Title

 

Clean House

Finally, let’s hide all of the trickeration and remove the clutter. You can hide the Data table completely. Hide Column1 and SelectedIndex in the Calculations table and the Attribute and Invoice columns in the Bridge table so your solution now looks clean and usable.

Picture11

Cleaned up report

Since we have occupied so much of your valuable time, we will forgo the steps to make this report look sexy.  You can add your own style, maybe use a Chiclet slicer (which I love) instead of the default Slicer visual and you have something that is extremely flexible and is starting to look pretty good as well.

Final version of report

Final version of report

DOWNLOAD THE PBIX

Brad Marshall

Brad Marshall is a Principal Consultant for PowerPivotPro and the President of The Sapient Company, an IT consulting company specializing in the Microsoft data platform including SQL Server, Power BI and Azure. With over 27 years of experience in multiple industries, Brad has a unique perspective on the Business/IT relationship and has a passion for enabling business through the application of technology.

This Post Has 32 Comments
  1. Excellent article and extremely helpful. I have requests for these types of reports all of the time. Will start putting this to use right away. Thanks!

  2. Thanks for a good showcase.

    …also tested with ‘Small Multiples, Bars’ (Infographic Designer).
    Measure2 =
    VAR SelectedIndex = MIN( ‘Slicer, Measures'[Index] )
    RETURN
    SWITCH( SelectedIndex ;
    1; [Total Amount] ;
    2; [Total Cost] )

  3. Brad.. Great article. I love using slicers like this. Haven’t done in PBI yet but have done if PP a few times. Most complex was where client (a vineyard) wanted to see his data by either Cash/Accrual and then by Dollars/ byVines/byAcres. Lots of measures, but Switch was saving grace and it works great. Am anxious to convert to PBI.

  4. wouldn’t it be easy if we over lap the 3 visuals on top of each other and use the bookmarking feature just to show relevant selection ?

    1. Tejas, functionally yes, this would achieve the same result. In this solution, if other columns were brought into the data table then they would show up automatically and be part of the dynamic attribute slicer. If we went the route of bookmarks then we would have to create a visual and work through the whole bookmark/toggle for each additional visual.

  5. Awesome Brad!! This is really cool thing. Is ther any way we can let users choose what columns they want from dataset in the report?

    Like, if there is a table with 5 fields, I pick 5 of them and create a report. Once this table gets more fields, can report pull those fields without selecting them in design. Please let me know if it is doable. Appreciate your help !!

    1. Hi Praveen, not sure about users choosing what columns from data set but since the unpivot is using Table.UnpivotOtherColumns then any column that shows up will end up being included in the bridge and attribute table and then would be part of the slicer, so yes, additional fields should show up automatically.

  6. Great stuff. 1) can this be done with Visual Studio DTS? 2) are there practical limitations. I have a FACT table with 100 million rows, 50 measures and 20 dimensions broken out.

    Even if I make a bridge table for 3 dimensions and 3 measures, that is alot rows!

    1) How would this be used with DIMENSIONS?
    2) How do you automate it? It still seems you need to create the bridge every time your Cube processes.

    1. Hi Thurston, it could be done with SQL Server 2017 and SSDT in the 2017 compatibility mode since that comes now with the Query Editor functionality. I haven’t used this technique on data with the number of rows that you mentioned but I assume there would be a price to pay. You could build the bridge and attribute slicer table in an ETL process instead of doing it in query editor.

  7. Thank you so much for sharing this. It is a truly clever design hack and I am looking forward to using it in my reports. The explanation was clear and fun to work through.
    I made one minor change, instead of duplicating each query, I set up each query to reference the previous one. That way, then if my data changes, I only need to fix the first query. Is there any disadvantage to using references rather than duplicating? Would this save any space or processing time on a very large data set?

    1. Thanks for the comments Kerry. Glad you found it helpful. Yes, I agree with you. Reference as opposed to duplicating is the way to go if code/step changes are not needed. Basically, reference will refer to the end result and not duplicate any of the code/steps. To my knowledge, referencing does not create another object in memory where duplicate does.

  8. Thanks for a very usefull article. It works amazingly!
    I’ve been looking for several month how to do it in powerpivot. Sorry for the possibly dummy question but is it possible to do the same trick in powerpivot without Power BI? It seems that relationship type matters and powerpivot supports only one type of it. Is there a solution for power pivot only without Power BI?

    1. George, thanks for the comments. Sam, a reader of our blog post commented on my post and sent me an example of doing this same sort of thing in Excel/PowerPivot. If you can send an email to [email protected] I will reply back with his example.

  9. I would typically approach this type of functionality in Power Pivot using hierarchies and MDX sets. I’m not sure you can do the same in Power BI, but it seems like a simpler approach in PP.

  10. Trying to get this to work in a star schema environment. How do I build the Bridge table, if my data table contains Region ID, and I have a separate Region table with the actual region names. Can’t figure out how to get the Region name onto the bridge table instead of the Region ID.

    1. Hi Ben, my first thought would be to do all of the steps involved in the post and then once you have your final bridge table do either a query editor function to lookup the Region name or do a calculated column in DAX that gets the Region name. You might could also do a merge query and pull in the Region name from that dimension table

  11. This is indeed a great article, thanks for the details. Is there a way to create a Bridge Attributes tables only from calculated set of measures?

  12. Its very good and helpful article. I have two questions Brad, might be silly but want to check before I assume anything :
    1. If we refresh the report with new data does the Bridge table automatically get the latest data from the main data table?
    2. If we want to add new Attribute to the Bridge table do we need to do the whole process again starting from duplicating the main data table or can we add to the existing table, if so how we can do this please? i.e currently your attribute has Region and Industry and if we want to add an other attribute lets say Sales Manager can we add this to existing bridge table?

Leave a Comment or Question