How To Dynamically Change Excel Chart Data

Users often demand to encounter the data in different angles to find the business concern insights they're looking for. Instead of creating dozens of visuals in your reports, solutions exist to reduce the number of visuals while giving users command over it.

In this postal service, I'll detail 4 different ways to achieve this, and I'll discuss the pros and cons of each solution. The solutions also work if y'all want to dynamically alter chart centrality. In this example, I'll dynamically change table'due south rows.

Business organization Specifications

I'll accept the Run a risk Works SQL database for this example, and nosotros'll attempt to slice the FactResellerSales SalesAmount, based on 5 fields:

EnglishProductSubcategoryName
EnglishProductCategoryName
SalesTerritoryGroup
SalesTerritoryRegion
SalesTerritoryCountry

As I'm writing this post (July 2019), there is no easy way to give users the ability to change a tabular array'due south rows from Power BI Services. I've seen that this feature is coming, as in this video, we're seeing a demo where they modify a visual on the fly, changing the axis of a chart.

Nonetheless, this business organisation specification can still be valid after the release of this characteristic, as users can be overwhelmed by the number of tables and fields bachelor in the information model. In our business example, we want them to exist able to select between 5 fields only.

Exploring dissimilar solutions

Rows Hierarchy

The easiest solution is to leverage the hierarchy feature on visuals. In our example, we simply need to put our five columns on the rows, and explain users that rows are going to change when they click on "Get to the adjacent level in the hierarchy".

When clicking on it, here's what happens. Rows inverse from category name to subcategory name.

This solution isn't ideal because in Power BI Services, users won't exist able to see the bureaucracy, and if they want to divide the tabular array by country, they'll have to click 4 times to the same button. The option is piece of cake to implement but not convenient at all in an App.

Bookmarks

Bookmarks heighten a bit the previous solution, making it a flake more than convenient. I won't particular the steps here as it's piece of cake to implement. Here'south the result:

This solution has a major drawback: maintenance. Chances are if y'all first using this bookmark fox, you'll end up having bookmarks all over the identify, and it tin can be a nightmare to maintain.

Plus, bookmarks take some space in a report, and they'll go a problem if yous want users to exist able to choose between 20 columns rather than 5. Overall, the solution with bookmark is a good patch, but isn't a scalable and maintainable solution.

The next 2 solutions are more scalable, as the users volition be able to pick the column name from a slicer. Slicer don't eat much space in the written report, and you tin can search values in it. Hence, the next solutions are fashion more than scalable.

Unpivoting a table

If we wanted to modify the table'south rows from columns available in a single table – for example, if we simply wanted these 3 columns from the DimSalesTerritory table: SalesTerritoryGroup, SalesTerritoryRegion and SalesTerritoryCountry – another solution worth mentioning is to create a new table by unpivoting all columns we desire to apply in our slicer.

To achieve this, I duplicated the DimSalesTerritory table and renamed the new i as DimSalesTerritoryUnpivoted. I selected the 3 columns, and clicked on Unpivot columns. And then I kept three columns, which ends up by having the post-obit table:

And here's how this table is linked to the data model.

I changed the cantankerous-filter management from single to both, because we'll use the Attribute column from the new tabular array to filter the DimSalesTerritory table. That's right, the attribute column is our slicer, and in our visual, the value cavalcade from the new tabular array is our dynamic rows.

In the cease, users take the flexibility to select a cavalcade in the slicer, and the matrix tabular array is updated based on the user selection.

Even though this solution works fine with one tabular array, information technology doesn't work in our example, where we need five columns from 3 distinct tables. The next solution, similar to this one, solves this issue.

DAX Tabular array and Mensurate

With this final solution, nosotros'll exercise something very similar to what's above, but we'll make it piece of work with columns from different tables.

The first footstep is to create a tabular array with all columns name in a column, and all corresponding values in some other column. We don't need any keys, as we won't link this table to the model. We'll create afterward a DynamicSumSalesAmount mensurate to leverage this new table.

Nosotros could create this table past unpivoting all 5 columns, and appending the iii queries (CategoryUnpivoted, SubCategoryUnpivoted and SalesTerritoryUnpivoted). However, that would create 3 unnecessary tables, and if nosotros wanted afterward to add a few columns, that would exist painful. Rather, we can create a DAX table to achieve the aforementioned affair.

Information technology gives us something like this:

Now we demand to create a measure on the FactResellerSales tabular array to leverage this DAX tabular array. The pull a fast one on is to use the TREATAS DAX role, which "Treats the columns of the input table as columns from other tables. For each column, filters out whatever values that are not present in its respective output column."

On the matrix tabular array, I put the slicer table values in rows, and the SumSalesAmount (Dynamic Slicer) in values. The Slicer table type should be placed as a slicer. In the end, the result is fast, user friendly, and doesn't change the information model drastically.

In an enterprise data model, you lot could use this DAX table with dozens of columns, and employ it to dynamically filter visual in any report. You just demand to recall to update the measure out that use this table whenever you add together or remove a cavalcade in the tabular array. To employ this solution with many reports, the play a trick on is to add a visual level filter to select columns yous want your users to be able to modify the visual with.

Your model won't exist whatever bigger with this solution every bit long as you lot don't add any column that have many unlike values (like customer name or date for example). In this example, my slicer table has 65 rows.

File

In this file beneath, you'll find the 4 different solutions put together into a PBIX file.