Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
JLarkin
Frequent Visitor

Dynamic Title Switcher

Hi all, 

 

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

JLarkin_0-1677850819230.png

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:
JLarkin_1-1677851088932.png

 

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

1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

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"

 

 

View solution in original post

10 REPLIES 10
tamerj1
Super User
Super User

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"

 

 

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.

Hi @mes0012 

Please elaborate further on your requirements. 

Hi @tamerj1 ,

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

- If singular month selected it appears as below

mes0012_3-1694681300118.png

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

mes0012_4-1694681326116.png

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

mes0012_2-1694681263234.png

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".

mes0012_6-1694681586102.png

mes0012_5-1694681570772.png

Hopefully this makes more sense! 

 

Thanks,

Matt.

 

@mes0012 

Try to wrap the formula with 

IF (

ISFILTERED ( 'Table'[Month] ),

'Original formula',

"YTD"

)

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

ShahabHoghooghi
Frequent Visitor

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)

 

 

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:

JLarkin_0-1678095119503.png

 

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

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.

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. 

JLarkin_0-1678793261433.png

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Kudoed Authors