Creating Dynamic Title for visual using calculated mesure

[AlixB]

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?

[v-rzhou-msft]

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.

[Greg_Deckler · Accepted solution]

@AlixB Check this out: https://community.powerbi.com/t5/Quick-Measures-Gallery/The-Most-Amazing-Mind-Blowing-Dynamic-Slicer....

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

[AlixB]

@Greg_Deckler 

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