The April 2019 update to Power BI has many good features, as this blog explains
Part two of a nine-part series of blogs

The April 2019 update to Power BI introduces a new concept: the ability to make the value of a property dynamic. Other welcome new changes include M Intellisense, data profiling in query editor and cross-report drill-down.

  1. The April 2019 Update to Power BI Desktop
  2. Conditional titles for visuals and conditional URLs (this blog)
  3. Intellisense in the M Query Editor Language
  4. Data profiling in Query Editor
  5. Cross-report drill-through
  6. Fuzzy merging
  7. Power BI Dataflows
  8. Other changes in the April 2019 update
  9. Power BI features waiting in preview, as of April 2019

For a cumulative list of all of the updates to Power BI Desktop in the last few years see this blog, or have a look at the Power BI courses that we run.

Posted by Andy Brown on 18 April 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.

Conditional titles for visuals and conditional URLs

This is a massive change of direction for Power BI, smuggled in as a solution to a simple problem.  It's worth reading this article because it gives a good insight into the direction Power BI is taking.

The problem solved by conditional titles and URLs

If you want to make the title for a visual make reference to the choices you've made in a slicer, up to now you've had to put the title in a separate card:

Variable title

Here the title in the middle is in a separate card visual, and doesn't automatically have the same width as the bottom table.

 

You've also been unable to make an action button go to a dynamic URL:

Conditional URL

Up to now, the URL assigned to an action button has been fixed (so it can't, for example, reference the region chosen in the slicer).

 

Dynamic properties - first create the necessary measures

To make the title and URL properties dynamic, you first need to add one or more measures to a table (I've created a new measures table, and added my measures to that):

New measures

The two new measures created for this example (see below for the formulae for each).

 

Here's what the ChosenRegion measure looks like:

ChosenRegion =

"Sales for towns in " &

SELECTEDVALUE(tblRegion[RegionName])

And here's what the ChosenRegionId measure looks like:

ChosenRegionId =

"https://www.intelligent-hibou.co.uk?regionid=" &

SELECTEDVALUE(tblRegion[RegionId])

Note that the measures won't show the region name or region id selected if you select more than one region at a time, so you'll need to set the region slicer to be single-select only:

Single-select slicer

Use the new facility rolled out last month to prevent a user selecting more than one region at a time in the slicer.

 

If you want to show multiple regions selected you'll need to create a more complicated measure, using either quick measures or DAX.

Setting conditional titles

Having done the hard work, here's how to make the title for a visual into an expression:

Conditional formatting

Right-click in the Title text property and choose Conditional formatting (even though it isn't formatting at all - see my rant below).

 

You can then choose to show the results of the measure in your title:

Chosen region as title

If you're wondering what the other options are apart from Field value, the answer is that there aren't any! Not yet, anyway.

 

Yeah!  We have a title whose text will change according to which region you have selected:

Variable title

The title reflects the slicer choice (or that made in any other filter, such as clicking on a region name in a chart or table).

 

Here's what the title now looks like:

Expression in property

To see the expression used is you have to right-click again on the property.

 

Setting conditional URLs

Conditional URLs work in the same way.  First create an action button:

An action button

A modest action button.

 

Make this of type Web URL, and then right-click on the Web URL property:

Setting URL formatting

As for a visual title, right-click to assign a measure to the title.

 

 

Choose the measure you've created:

Assign a measure

Here I've chosen my ChosenRegionId measure, the formula for which is shown above.

 

And here's what you now get when you click on the button with (for example) the South-West region number 7 selected in the slicer:

URL with region id

The URL reflects the region chosen (well, after translating the URL into English).

The future for Power BI

Here's what Microsoft say:

Over the coming months we'll be rolling these conditional formatting options out to more properties on more visuals and give you more ways to set the expression. The goal is that you'll be able to use rules, a measure or enter an expression directly in the dialog and use the result to format any property.

So expect to see the ability in future months to make the font size, colour, border and every other property of a visual into an expression.  To give you an idea of where Power BI is headed, here's a screen-shot from SQL Server Reporting Services, which does this elegantly:

Expression for property

Virtually every property can be set to be an expression in SSRS.

 

My objections to this new approach

The idea behind this is great - being able to make every property adapt to the data being displayed in a report.  However, why have Microsoft called it formatting?

Formatting tooltip

This has got nothing to do with formatting!

 

Instead, I'd have liked to see a little tool appearing to the right of any property whose value can be set to be an expression, as in SSRS:

Expressions in SSRS

Another screen-shot from SSRS, showing three properties each of which can be set to an expression.

 

Microsoft, are you listening?  And can I have commission if you go with my suggestion?

I have one other worry about this way of implementing variable values for properties.  To use it effectively, most people will need to learn DAX, which is no small undertaking.  Is there scope for some sort of simpler expression language?

This blog has 0 threads Add post