Change Title to Slicer if Slicer is used

[DebbieE]

Hi all,

i have a Column in my dim table containing a header for a table. So I show this over a visual

BUT if the user chooses the year and Month from the date Slicer I want it to show this instead

The example is that the title normally says

Dates from 2020 through to 2024

As soon as the user slices for example 1 year. I want it to say 2023

If a user Slices a year and some months in that year I want it to say 2023 March April

If a user slices multiple things I want it to say 2023 March April 2024 January

Or something along those lines.

Is this possible at all?

[MFelix]

Hi @DebbieE ,

For this you need to make a measure that you will use dinamically on your title.

Check this video from Guy in the Cube with Alberto Ferrari

[DebbieE]

This is great but it only does months,. It doesnt do months and years and its already really complex. I dont know how to add in years to the mix?

And then the next step for this question is how to show something else. unless the Years / Months are selected?

[MFelix]

Hi Debbie,

I did this formula:

Date Information = VAR _maximumdate = MAX('Query1'[Date])
	VAR _minimumdate = MIN('Query1'[Date])
	VAR _nmonths = COUNTROWS(DISTINCT('Query1'[Year Month]))
	VAR _nyears = COUNTROWS(DISTINCT('Query1'[Year]))
	VAR _ntotalrows = COUNTROWS('Query1')
	VAR _nrowsselected = CALCULATE(
		COUNTROWS('Query1'),
		DATESBETWEEN(
			'Query1'[Date],
			_minimumdate,
			_maximumdate
		),
		REMOVEFILTERS('Query1')
	)
	RETURN
		SWITCH(
			TRUE(),
			_nyears = 1 && _nmonths = 12, FORMAT(
				_maximumdate,
				"YYYY"
			),
			_nyears = 1 && _nmonths < 12, MAX('Query1'[Year]) & ": " & CONCATENATEX(
				DISTINCT('Query1'[End of Month]),
				FORMAT(
					'Query1'[End of Month],
					"MMMM"
				),
				", "
			),
			_nmonths = 1, MIN('Query1'[Year Month]),
			_nrowsselected = _ntotalrows, FORMAT(
				_minimumdate,
				"YYYY MMMM"
			) & " to " & FORMAT(
				_maximumdate,
				"YYYY MMMM"
			),
			CONCATENATEX(
				SUMMARIZE(
					'Query1',
					'Query1'[Year],
					"D", CONCATENATEX(
						SUMMARIZE(
							'Query1',
							'Query1'[End of Month]
						),
						FORMAT(
							'Query1'[End of Month],
							"MMMM"
						),
						", "
					)
				),
				" " & 'Query1'[Year] & ": " & [D],
				UNICHAR(10)
			)
		)

Check the result in the PBIX attach.

[DebbieE]

Apologies I have been on Annual leave. Thank you so much for this. However I can already see that this is way to complex for me to achieve in the time i have got.

Im a little confused by End of Month since its not something I have. Would I have to create this in the date dimension as an actual item?

[MFelix]

This needs to be a complex formula based on the selected values, you can use the concatanex to make the formula. 

I will try to setup an example