This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

Creating reports in Power BI with dynamic measures or dynamic dimensions Part two 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. Making Power BI reports dynamic How to create dynamic measures (this blog) Creating a Power BI report with dynamic dimensions

Creating reports in Power BI with dynamic measures or dynamic dimensions

Part two 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.

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

Posted by Andy Brown on 03 January 2019

How to create dynamic measures

This is much the easier of the two problems, although it does require a working knowledge of DAX.

Step 1 - create a table of choices

The first thing to do is to create a slicer allowing you to choose a measure. Here in outline is how to do this. First create a new table:

Click on the Enter Data tool to create a table.

Type in a table name, column name and values such that when you finish you have a table of all the statistics that you might want to show:

The final table of measures, when you've finished creating it.

Step 2 - allowing a user to choose a measure

The next step is to create a slicer based on this table:

A suggested slicer, allowing you to choose one measure to display.

Note that one problem with the standard Power BI slicer is that a user can select more than one measure (or no measures at all). One way to get round is to use the Chiclet Slicer custom visual, which lets you force a user to select one (and one only) item.

Now create a measure which allows you to show a suitable title:

Title = IF(

// if someone has chosen exactly one measure ...

HASONEVALUE(Choices[Choice]),

// ... display its value ...

VALUES(Choices[Choice]),

// ... otherwise show an error message

"You must choose one measure"

)

You can now create a card and set this to display the value of your measure:

The card now displays the name of whichever measure you selected.

Step 3 - getting the chosen measure to display in your visual

The final bit of magic is to create a measure which displays a different statistic according to the measure you selected in the slicer:

Measure to show = IF(

// if someone chose a single measure ...

HASONEVALUE(Choices[Choice]),

SWITCH(

// test its value (ie what user chose)

VALUES(Choices[Choice]),

// if it's "Total sales", display the sum of sales

"Total sales", SUM(SalesData[Sales]),

// if it's "Average price", show the average sales

"Average price", AVERAGE(SalesData[Sales]),

// if it's "Number of sales", count the sales rows

"Number of sales", COUNT(SalesData[Id])

BLANK()

)

Now select your table (or any other visual):

We want this table to show the statistic we've chosen.

You can then use this visual to display the measure you've created:

Choose to display your measure in the table.

And as they (used to) say in the UK, Bob's your uncle!

Parts of this blog
Making Power BI reports dynamic How to create dynamic measures (this blog) Creating a Power BI report with dynamic dimensions

Some other pages relevant to the above blogs include:

This blog has 0 threads

Add a new post

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC