Forum Discussion

JLarkin's avatar
JLarkin
Frequent Visitor
3 years ago
Solved

Dynamic Title Switcher

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

  • tamerj1's avatar
    tamerj1
    3 years ago

    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"

     

     

10 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    3 years ago

    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"

     

     

    • mes0012's avatar
      mes0012
      Frequent Visitor
      2 years ago

      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.

  • ShahabHoghooghi's avatar
    ShahabHoghooghi
    Frequent Visitor
    3 years ago

    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)

     

     

    • JLarkin's avatar
      JLarkin
      Frequent Visitor
      3 years ago

      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

      • ShahabHoghooghi's avatar
        ShahabHoghooghi
        Frequent Visitor
        3 years ago

        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.