Forum Discussion

Marshmallow's avatar
Marshmallow
Helper II
1 year ago
Solved

Dynamic title on date hierarcy

Hi  I am trying to get dynamic title based on the date selected (which is a date hierarcy). On the Date hierarchy i selected: Year and Month.    Then I proceed with my formula is: Dynamic_...
Need Help
  • ajaybabuinturi's avatar
    ajaybabuinturi
    1 year ago

    Hi Marshmallow, try with this logic

    test1 =
VAR selectedmonthyear = values ('Calendar'[Month Year])
VAR MaxToShow = 3
VAR TotalSelected = COUNTROWS(selectedmonthyear)
VAR PeriodsToShow = TOPN(MaxToShow, selectedmonthyear, 'Calendar'[Month Year], ASC)
VAR MorePeriods = IF(TotalSelected > MaxToShow, " and " & (TotalSelected - MaxToShow) & " more", "")
VAR Result = 
"New Applications Received in: " & 
IF(TotalSelected = COUNTROWS(ALL('Calendar'[Month Year])),
"All",
CONCATENATEX(PeriodsToShow, 'Calendar'[Month Year], ", ") & MorePeriods
)
RETURN
Result

     

    Thanks,
    If you found this solution helpful, please consider giving it a Like👍 and marking it as Accepted Solution✔. This helps improve visibility for others who may be encountering/facing same questions/issues.

ajaybabuinturi's avatar
ajaybabuinturi
Super User
1 year ago

Hi Marshmallow,
Instead using Date hierarchy in the slicer, use Date [Month Year] field to get the dynamic title by Month Year.

Try with this

Dynamic_title_Appl_received = "New Applications Received in: " & 
VAR MaxValue = 3
VAR vcountSubCategory = DISTINCTCOUNT('Calendar'[Month Year])
VAR EndName = IF(vcountSubCategory > MaxValue, 
                " and " & (vcountSubCategory - MaxValue) & " other periods",
                "")
VAR Result = 
IF(ISFILTERED('Calendar'[Month Year]),
CONCATENATEX(TOPN(MaxValue, VALUES('Calendar'[Month Year])),'Calendar'[Month Year],", ") & EndName, "All Periods")

RETURN
Result

 

Thanks,
If you found this solution helpful, please consider giving it a Like👍 and marking it as Accepted Solution✔. This helps improve visibility for others who may be encountering/facing same questions/issues.

Marshmallow's avatar
Marshmallow
Helper II
1 year ago

Hi, it didn't work. However I have since come up with a solution however I need to limit the output to only shows 3 periods. are you able to assist? This is my new code:

 

test1 =
VAR selectedmonthyear =
    values ('Calendar'[Month Year])
    return
    "New Applications Received in: "
    & if(
        COUNTROWS(selectedmonthyear) = COUNTROWS(all('Calendar'[Month Year])), "All",
        CONCATENATEX(VALUES('Calendar'[Month Year]), 'Calendar'[Month Year], ","))
  • ajaybabuinturi's avatar
    ajaybabuinturi
    Super User
    1 year ago

    Hi Marshmallow, try with this logic

    test1 =
VAR selectedmonthyear = values ('Calendar'[Month Year])
VAR MaxToShow = 3
VAR TotalSelected = COUNTROWS(selectedmonthyear)
VAR PeriodsToShow = TOPN(MaxToShow, selectedmonthyear, 'Calendar'[Month Year], ASC)
VAR MorePeriods = IF(TotalSelected > MaxToShow, " and " & (TotalSelected - MaxToShow) & " more", "")
VAR Result = 
"New Applications Received in: " & 
IF(TotalSelected = COUNTROWS(ALL('Calendar'[Month Year])),
"All",
CONCATENATEX(PeriodsToShow, 'Calendar'[Month Year], ", ") & MorePeriods
)
RETURN
Result

     

    Thanks,
    If you found this solution helpful, please consider giving it a Like👍 and marking it as Accepted Solution✔. This helps improve visibility for others who may be encountering/facing same questions/issues.