Creating reports in Power BI with dynamic measures or dynamic dimensions
Part three of a three-part series of blogs

This blog shows how to create fully dynamic reports, whereby users can choose which statistics to show and which dimensions to report them by.

  1. Making Power BI reports dynamic
  2. How to create dynamic measures
  3. Creating a Power BI report with dynamic dimensions (this blog)

Posted by Andy Brown on 03 January 2019

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Creating a Power BI report with dynamic dimensions

To get this to work, we'll have to create two new tables:

Two new tables

The Choices table will contain the dimensions you can report by, and the SalesJoined table will combine all of the centre types, all of the regions and all of the products. To see how this all works, read on!

Step 1 - creating a table of choices

The first thing to do is to create a table giving a user a choice of which dimension to report by:

Enter data tool

Click on the Enter Data tool to create a new table.

Type in one value for each dimension a user could choose to report by:

Choices table

Here a user can choose to summarise sales by centre type, product or region.

Step 2 - create a slicer and card

Having created a table of choices, you can now create a slicer allowing a user to choose one of them:

Slicer

As for the previous example, the only way to prevent a user choosing more than one dimension at a time would be to use a Chiclet Slicer custom visual.

To give your visual a nice title, create a separate card:

Title for card

Although this looks like a title for the visual, it's actually a separate visual with the same width.

Create a measure using the following formula:

Title = IF(

HASONEVALUE(Choices[Choice]),

"Sales by " & SELECTEDVALUE(Choices[Choice]),

"Choose one value in the slicer"

)

If you display this measure on your card, it should show Sales by Centre type, Sales by Product or Sales by Region.

Step 3 - create a version of the sales data table for each dimension

Our eventual aim is to create a table which lists all the products, centre types and regions in a single table:

Combined tables

The start of the combined table (the ChoiceMade column will give the words Product, Centre type or Region).

To do this, first go into Query Editor.  You should have a single copy of the SalesData table:

Sales data table

This table shows which region, product and centre type each sale belongs to.

Right-click on this table and create 3 duplicates:

Sales data duplicate

Call your duplicate tables SalesData Region, SalesData Product and SalesData CentreType.

 

Step 4 - transform each of these dimension tables

Here's how to transform the SalesData Product table (you'd do analogous steps for the other two dimension tables).  First add a new column saying what sort of a thing each row is:

Adding a column

Choose this option on the Add Column tab.

 

Type in Product twice:

New literal column

By the time you've done this twice, you'll be able to choose OK to create the new column.

Rename this new column from Literal to Dimension:

Renaming a column

Change the column name to something more meaningful.

Repeat the above process for the SalesData Region and SalesData CentreType tables.

Step 5 - Combine the dimension tables

You now need to create a single table containing all of the products, regions and centre types.

For those who know SQL, what you're doing is creating a UNION ALL query to combine the rows from the 3 dimension tables.

To do this, select one of the tables:

Choosing a table

It doesn't matter which of the 3 tables you choose first.

 

On the Home tab of the Query Editor ribbon, choose to append data:

Append query as new

Choose to create a new query, appending data from existing tables.

 

Choose Three or more tables, and select the tables whose data you want to append:

Appending 3 tables

Here we're appending product sales data, centre type sales data and regional sales data.

You can now tell Power BI not to bother loading data for the 3 source queries:

Disable load

There's no point in loading separate copies of the sales data.

 

Step 6 - Create relationships

Back in Power BI proper, you should now create the relationship shown:

Linking the choice field

The relationship between the Choices table and the sales data ensures that when a user chooses a dimension in the slicer, only sales data for this dimension appears in the visual.

Step 7 - creating a generic calculated column

You can't make the aggregating field in a visual dynamic:

A visual detail field

The Details field has to have the same name, regardless of whether a user chose to show sales by product, region or centre type.

 

To get this to work, create this new column in the SalesJoined table:

Entity name column

The formula for the new column will display the centre type, product or region according to the choice a user makes in the slicer.

Step 8 - create the visual!

Finally you can now create the visual:

The pie chart visual

The visual sums sales data by the selected entity name.

It's been a long journey, but you have now arrived! 

  1. Making Power BI reports dynamic
  2. How to create dynamic measures
  3. Creating a Power BI report with dynamic dimensions (this blog)
This blog has 0 threads Add post