topic Re: Dynamic Title Switcher in DAX Commands and Tips

Dynamic Title Switcher

JLarkin — Fri, 03 Mar 2023 13:46:42 GMT

Hi all,

I was hoping to get help with a dynamic title of month and year, like so:

This is currently made using the following two formulae:

Title - Selection =

CONCATENATEX(

VALUES(DATE_DIM[MNTH_NAME]),

DATE_DIM[MNTH_NAME],

" & ",

DATE_DIM[MNTH_NAME])

and

Title = [Title - Selection] & " " & SELECTEDVALUE('DATE_DIM'[YR_NBR]) & " - Total Sales Bookings"

The problem arises when I begin to select multiple months like so:

Ideally, the formula would be able to sort the selected value alphabetically when only two months are included, and also use [QTR_NBR] if 3 months in the one quarter were selected.

Any assistance would be greatly appreciated!

Thanks,

James

Re: Dynamic Title Switcher

ShahabHoghooghi — Fri, 03 Mar 2023 15:37:26 GMT

Hi,

Try This Dax Code Instead:

Title - Selection = VAR __MonthOrder = LOOKUPVALUE(DATE_DIM[MNTH_NUMBER], DATE_DIM[MNTH_NAME], SELECTEDVALUE(DATE_DIM[MNTH_NAME])) RETURN CONCATENATEX( VALUES(DATE_DIM[MNTH_NAME]), DATE_DIM[MNTH_NAME], " & ", __MonthOrder, ASC)

Re: Dynamic Title Switcher

JLarkin — Mon, 06 Mar 2023 09:33:42 GMT

Hi ShahabHoghooghi,

Thank you for your response but this is unfortunately not what I need. The ASC does not take into account the sort column I have on the months, and then does it by alphabetical order. I also require for the title to switch to using the column [QTR_NBR] when 3 months in the same QTR are selected:

For example, selecting the first two months should display "January & February - Total Sales..", and selecting the first three should display "Q1 - Total Sales.."

Many thanks,

James

Re: Dynamic Title Switcher

ShahabHoghooghi — Wed, 08 Mar 2023 12:33:23 GMT

No, If you have a look at the variable I defined, I used lookup value, then for example it lookup when the month name is March, then it gives you the month number column for that specefic selection, which is 3. It means that, this code will be sorted based on Month Number (numerical) not Month Name and will not be alphabetical.

The second concat measure you created is fine. you just need to change the first one and use month number for sorting instead of month name.

Hope it works for you.

Re: Dynamic Title Switcher

JLarkin — Tue, 14 Mar 2023 11:29:23 GMT

Hi ShahabHoghooghi.
The textbox is using the [Title - Selection] variable you defined, and it still displays as Feb & Mar & Jan, instead of Jan & Feb & Mar.

Re: Dynamic Title Switcher

tamerj1 — Tue, 14 Mar 2023 12:02:15 GMT

Hi JLarkin
Please try

Title - Selection = CONCATENATEX ( VALUES ( DATE_DIM[MNTH_NAME] ), DATE_DIM[MNTH_NAME], " & ", CALCULATE ( MAX ( DATE_DIM[MNTH_NUMBER] ) ), ASC )

Title = IF ( COUNTROWS ( VALUES ( 'DATE_DIM'[MNTH_NAME] ) ) = 3 && COUNTROWS ( VALUES ( 'DATE_DIM'[QTR] ) ) = 1, MAX ( 'DATE_DIM'[QTR] ), [Title - Selection] ) & " " & SELECTEDVALUE ( 'DATE_DIM'[YR_NBR] ) & " - Total Sales Bookings"

Re: Dynamic Title Switcher

JLarkin — Tue, 14 Mar 2023 14:00:06 GMT

Hi tamerj1 and ShahabHoghooghi,
Thank you very much to both of you!

Re: Dynamic Title Switcher

mes0012 — Wed, 13 Sep 2023 11:06:23 GMT

Thanks tamerj1 i've been scratching my head on something similar for a while, this worked perfectly.

How would i go about layering in YTD? For example, if April - August are selected then return "YTD"?

Many thanks,

Matt.

Re: Dynamic Title Switcher

tamerj1 — Wed, 13 Sep 2023 17:56:18 GMT

Hi mes0012

Please elaborate further on your requirements.

Re: Dynamic Title Switcher

mes0012 — Thu, 14 Sep 2023 08:55:43 GMT

Hi tamerj1 ,

So i'm looking to build a dynamic title that breaks down as follows:

- If singular month selected it appears as below

- If two months are selected it appears in a list as below

- If the relevant 3 months are selected for the qtr then return the financial qtr as below

All of the above works perfectly using your original solution.

The bit i'm struggling with is layering in "YTD" if all months are selected. I currently have it structured as below when all months are selected. Instead of a list of the months selected i want it to return "YTD".

Hopefully this makes more sense!

Thanks,

Matt.

Re: Dynamic Title Switcher

tamerj1 — Thu, 14 Sep 2023 11:49:37 GMT

mes0012

Try to wrap the formula with

IF (

ISFILTERED ( 'Table'[Month] ),

'Original formula',

"YTD"

)