Forum Discussion

Marshmallow's avatar
Marshmallow
Icon for Helper II rankHelper 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_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")
    Return
    If(
        ISFILTERED('Calendar'[Month Year]),
    CONCATENATEX(
        TOPN(MaxValue, VALUES('Calendar'[Month Year])),
        ('Calendar'[Month Year]),
        ", "
        )&EndName,
        "All Periods")
 
 
Unfortunately it doesn't work..it jus says All Periods whenever I selected a period.
 
Can someone please help?
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.

3 Replies

  • ajaybabuinturi's avatar
    ajaybabuinturi
    Icon for Super User rankSuper 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
      Icon for Helper II rankHelper 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
        Icon for Super User rankSuper 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.