topic Re: Creating Dynamic Title for visual using calculated mesure in Developer

Creating Dynamic Title for visual using calculated mesure

AlixB — Mon, 09 Aug 2021 11:36:30 GMT

I'm trying to add a dynamic title to a visual, based on a selection of another visual, which I know how to do in theory, however the calculations to get it to work are causing a bit of a headache.

When a category in a table is clicked on, I want the title to show that category, however if no category is clicked I don't want the title to show all categories, but something like "All Categories"

My initial thought was to create a new measure with the calculation

Category = IF(DISTINCTCOUNT('Dataset1'[Account Category])>1,"All Categories",'Dataset1'[Account Category])

However this won't work because a measure doesn't allow me to use 'Dataset1'[Account Category] as the alternative outcome.

So I thought I'd create a measure and then a calculated column referencing the measure.

Category = IF(COUNT('Dataset1'[Account Category])>1,2,1)

TitleCategory = if('Dataset1'[Category]=2,"All Categories",'Dataser1'[Account Category])

However this doesn't work either as calculated columns can't reference a measure column with a count function.

So I'm stuck as to what to try next.

The logic is very simple but the system is not allowing the calculation.

Can anyone help please?

Re: Creating Dynamic Title for visual using calculated mesure

Greg_Deckler — Mon, 09 Aug 2021 12:03:05 GMT

@AlixB Check this out: https://community.powerbi.com/t5/Quick-Measures-Gallery/The-Most-Amazing-Mind-Blowing-Dynamic-Slicer-Title-Measure-Ever/m-p/429587#M154.

In your first formula, use MAX around your column at the end.

Re: Creating Dynamic Title for visual using calculated mesure

Anonymous — Wed, 11 Aug 2021 07:11:30 GMT

Hi @AlixB

In addition to Greg_Deckler 's reply, we need to create a measure to get dynamic result.

Measure will return aggregations, in measure we need to use Sum function to get number type value and use Max function to get text, date type values.

Try this measure:

Measure = IF(DISTINCTCOUNT('Dataset1'[Account Category])>1,"All Categories",MAX(Dataset1[Account Category]))

Result is as below.

By default:

Select Category A in other visual:

You can refer to this blog to get more details about the differences between calculated column and measure.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Creating Dynamic Title for visual using calculated mesure

AlixB — Mon, 16 Aug 2021 07:15:15 GMT

@Greg_Deckler

Thanks for that, it worked beautifully with a bit of tweaking.