Forum Discussion

eleftheriaK's avatar
eleftheriaK
New Member
3 years ago
Solved

Dynamic Title based on Hierarchical Slicer multiple selections

Hi all,

 

I have a slicer that uses this hierarchy: Half Year (H1, H2), Quarter (Q1, Q2, Q3, Q4), Month (Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec) and the user can make multiple selections, for example can select Q1 and Apr or H1 and Jul and Aug. I Want my title to change like this:

If the user has selected Jan - Mar for example, the title should say Q1 the same as if the user has selected Q1

If the user has selected Q1 and Q2, the title should say H1 the same as if the user has selected H1

If the user has selected Q1 and Apr, the title should say Jan - Apr or if has selected Q1 and May the title should say Q1 & May

If the user has selected H1 and Jul, the title should say Jan - Jul 

Can you please assist?

 

Thank you in advance

Eleftheria

Need Help
Tips and Tricks
  • eleftheriaK's avatar
    eleftheriaK
    3 years ago

    Hi,

     

    I made it work with this huge measure

    Title for time =
    VAR CurrentYear = [Current Year]
    VAR SelectedMonths = VALUES('Calendar_Table'[Month Name])
    VAR SelectedQs = VALUES('Calendar_Table'[Quarter_Name])
    VAR _CM = COUNTROWS(SelectedMonths)

    VAR _CurrentYearTable =
    CALCULATETABLE(
        ALL('Calendar_Table'),
        'Calendar_Table'[Year] = CurrentYear
    )

    VAR _Half1 =
    SUMMARIZE(
            FILTER(
                ALL('Calendar_Table'),
                'Calendar_Table'[Year] = CurrentYear &&
                'Calendar_Table'[Half_No] = 1
            ),
            'Calendar_Table'[Month Name]

    )

    VAR CountH1Months =
    COUNTROWS(
        INTERSECT(SelectedMonths,_Half1)
    )

    VAR Check_H1 =
    IF(
        COUNTROWS(
        INTERSECT(SelectedMonths,_Half1)
    ) = 6,
    "H1"
    )

    VAR _Half2 =
    SUMMARIZE(
            FILTER(
                ALL('Calendar_Table'),
                'Calendar_Table'[Year] = CurrentYear &&
                'Calendar_Table'[Half_No] = 2
            ),
            'Calendar_Table'[Month Name]

    )

    VAR Check_H2 =
    IF(
        COUNTROWS(
        INTERSECT(SelectedMonths,_Half2)
    ) = 6,
    "H2"
    )

    VAR HalfTitle =
    SWITCH(
        TRUE(),
        NOT(ISBLANK(Check_H1)),Check_H1,
        NOT(ISBLANK(Check_H2)),Check_H2,
        ISBLANK(Check_H1) || ISBLANK(Check_H2), CONCATENATEX(SelectedQs,'Calendar_Table'[Quarter_Name],", ")
    )

    VAR _Q1 =
    SUMMARIZE(
            FILTER(
                ALL('Calendar_Table'),
                'Calendar_Table'[Year] = CurrentYear &&
                'Calendar_Table'[Quarter] = 1
            ),
            'Calendar_Table'[Month Name]

    )

    VAR Check_Q1 =
    IF(
        COUNTROWS(
        INTERSECT(SelectedMonths,_Q1)
    ) = 3,
    "Q1"
    )  

    VAR _Q2 =
    SUMMARIZE(
            FILTER(
                ALL('Calendar_Table'),
                'Calendar_Table'[Year] = CurrentYear &&
                'Calendar_Table'[Quarter] = 2
            ),
            'Calendar_Table'[Month Name]

    )

    VAR Check_Q2 =
    IF(
        COUNTROWS(
        INTERSECT(SelectedMonths,_Q2)
    ) = 3,
    "Q2"
    )

    VAR _Q3 =
    SUMMARIZE(
            FILTER(
                ALL('Calendar_Table'),
                'Calendar_Table'[Year] = CurrentYear &&
                'Calendar_Table'[Quarter] = 3
            ),
            'Calendar_Table'[Month Name]

    )

    VAR Check_Q3 =
    IF(
        COUNTROWS(
        INTERSECT(SelectedMonths,_Q3)
    ) = 3,
    "Q3"
    )

    VAR _Q4 =
    SUMMARIZE(
            FILTER(
                ALL('Calendar_Table'),
                'Calendar_Table'[Year] = CurrentYear &&
                'Calendar_Table'[Quarter] = 4
            ),
            'Calendar_Table'[Month Name]

    )

    VAR Check_Q4 =
    IF(
        COUNTROWS(
        INTERSECT(SelectedMonths,_Q4)
    ) = 3,
    "Q4"
    )

    VAR _MorethanHalf =
    SWITCH(
        TRUE(),
        COUNTROWS(INTERSECT(SelectedMonths,_Half1)) = 6,"H1 & "&CONCATENATEX(EXCEPT(SelectedMonths,_Half1),'Calendar_Table'[Month Name],", "),
        COUNTROWS(INTERSECT(SelectedMonths,_Half2)) = 6,CONCATENATEX(EXCEPT(SelectedMonths,_Half2),'Calendar_Table'[Month Name],", ")&" & H2",
        NOT(ISBLANK(Check_Q1)) && NOT(ISBLANK(Check_Q3)),"Q1 & Q3 & "&CONCATENATEX(EXCEPT(SelectedMonths,UNION(_Q1,_Q3)),'Calendar_Table'[Month Name],", "),
        NOT(ISBLANK(Check_Q1)) && NOT(ISBLANK(Check_Q4)),"Q1 & Q4 & "&CONCATENATEX(EXCEPT(SelectedMonths,UNION(_Q1,_Q4)),'Calendar_Table'[Month Name],", "),
        NOT(ISBLANK(Check_Q2)) && NOT(ISBLANK(Check_Q3)),"Q2 & Q3 & "&CONCATENATEX(EXCEPT(SelectedMonths,UNION(_Q2,_Q3)),'Calendar_Table'[Month Name],", "),
        NOT(ISBLANK(Check_Q2)) && NOT(ISBLANK(Check_Q4)),"Q2 & Q4 & "&CONCATENATEX(EXCEPT(SelectedMonths,UNION(_Q2,_Q4)),'Calendar_Table'[Month Name],", ")
    )





    VAR QTitle =
    SWITCH(
        TRUE(),
        NOT(ISBLANK(Check_Q1)), Check_Q1,
        NOT(ISBLANK(Check_Q2)), Check_Q2,
        NOT(ISBLANK(Check_Q3)), Check_Q3,
        NOT(ISBLANK(Check_Q4)), Check_Q4,
        BLANK()
    )

    VAR QTitleH1 =
    SWITCH(
        TRUE(),
        NOT(ISBLANK(Check_Q1)), Check_Q1,
        NOT(ISBLANK(Check_Q2)), Check_Q2,
        BLANK()
    )

    VAR QTitleH2 =
    SWITCH(
        TRUE(),
        NOT(ISBLANK(Check_Q3)), Check_Q3,
        NOT(ISBLANK(Check_Q4)), Check_Q4,
        BLANK()
    )

    VAR _9Months =
    IF(
        COUNTROWS(
        INTERSECT(SelectedMonths,_Half1)
        ) = 6,
        "H1 & "&QTitleH2,
        QTitleH1&" & H2"
    )

    VAR _EXCEPTQ1 = EXCEPT(SelectedMonths,UNION(_Half2,_Q2))
    VAR _EXCEPTQ2 = EXCEPT(SelectedMonths,UNION(_Half2,_Q1))
    VAR _EXCEPTQ3 = EXCEPT(SelectedMonths,UNION(_Half1,_Q4))
    VAR _EXCEPTQ4 = EXCEPT(SelectedMonths,UNION(_Half1,_Q3))

    VAR _Morethan9Months =
    SWITCH(
        TRUE(),
        COUNTROWS(INTERSECT(SelectedMonths,_Half1)) = 6 && COUNTROWS(_EXCEPTQ3)>0,
        "H1 & "&Check_Q3&" & "&CONCATENATEX(EXCEPT(SelectedMonths,UNION(_Half1,_Q3)),'000 445_Calendar_Table'[Month Name],", "),
        COUNTROWS(INTERSECT(SelectedMonths,_Half1)) = 6 && COUNTROWS(_EXCEPTQ4)>0,
        "H1 & "&Check_Q4&" & "&CONCATENATEX(EXCEPT(SelectedMonths,UNION(_Half1,_Q4)),'Calendar_Table'[Month Name],", "),
        COUNTROWS(INTERSECT(SelectedMonths,_Half2)) = 6 && COUNTROWS(_EXCEPTQ1)>0,
        CONCATENATEX(EXCEPT(SelectedMonths,UNION(_Half2,_Q1)),'Calendar_Table'[Month Name],", ")&" & "&Check_Q1&" & H2",
        COUNTROWS(INTERSECT(SelectedMonths,_Half2)) = 6 && COUNTROWS(_EXCEPTQ2)>0,
        CONCATENATEX(EXCEPT(SelectedMonths,UNION(_Half2,_Q2)),'Calendar_Table'[Month Name],", ")&" & "&Check_Q2&" & H2",
        BLANK()
    )

    VAR _QandMonths =
    SWITCH(
        TRUE(),
        NOT(ISBLANK(Check_Q1)),Check_Q1&" & "&CONCATENATEX(EXCEPT(SelectedMonths,_Q1),'Calendar_Table'[Month Name],", "),
        NOT(ISBLANK(Check_Q2)),Check_Q2&" & "&CONCATENATEX(EXCEPT(SelectedMonths,_Q2),'Calendar_Table'[Month Name],", "),
        NOT(ISBLANK(Check_Q3)),Check_Q3&" & "&CONCATENATEX(EXCEPT(SelectedMonths,_Q3),'Calendar_Table'[Month Name],", "),
        NOT(ISBLANK(Check_Q4)),Check_Q4&" & "&CONCATENATEX(EXCEPT(SelectedMonths,_Q4),'Calendar_Table'[Month Name],", ")
    )

    VAR Result =
    SWITCH(
        TRUE(),
        _CM = 12,"Total Year",
        _CM>9, _Morethan9Months,
        _CM = 9, _9Months,
        _CM>6 && _CM<9, _MorethanHalf,
        _CM = 6,HalfTitle,
        _CM>3 && _CM<6, _QandMonths,
        _CM = 3,QTitle,
        CONCATENATEX(SelectedMonths,'Calendar_Table'[Month Name],", ")
       
    )


    RETURN
    Result
     
    If anyone has a better solution please enlighten me!

2 Replies

  • eleftheriaK's avatar
    eleftheriaK
    New Member
    3 years ago

    Hi,

     

    I made it work with this huge measure

    Title for time =
    VAR CurrentYear = [Current Year]
    VAR SelectedMonths = VALUES('Calendar_Table'[Month Name])
    VAR SelectedQs = VALUES('Calendar_Table'[Quarter_Name])
    VAR _CM = COUNTROWS(SelectedMonths)

    VAR _CurrentYearTable =
    CALCULATETABLE(
        ALL('Calendar_Table'),
        'Calendar_Table'[Year] = CurrentYear
    )

    VAR _Half1 =
    SUMMARIZE(
            FILTER(
                ALL('Calendar_Table'),
                'Calendar_Table'[Year] = CurrentYear &&
                'Calendar_Table'[Half_No] = 1
            ),
            'Calendar_Table'[Month Name]

    )

    VAR CountH1Months =
    COUNTROWS(
        INTERSECT(SelectedMonths,_Half1)
    )

    VAR Check_H1 =
    IF(
        COUNTROWS(
        INTERSECT(SelectedMonths,_Half1)
    ) = 6,
    "H1"
    )

    VAR _Half2 =
    SUMMARIZE(
            FILTER(
                ALL('Calendar_Table'),
                'Calendar_Table'[Year] = CurrentYear &&
                'Calendar_Table'[Half_No] = 2
            ),
            'Calendar_Table'[Month Name]

    )

    VAR Check_H2 =
    IF(
        COUNTROWS(
        INTERSECT(SelectedMonths,_Half2)
    ) = 6,
    "H2"
    )

    VAR HalfTitle =
    SWITCH(
        TRUE(),
        NOT(ISBLANK(Check_H1)),Check_H1,
        NOT(ISBLANK(Check_H2)),Check_H2,
        ISBLANK(Check_H1) || ISBLANK(Check_H2), CONCATENATEX(SelectedQs,'Calendar_Table'[Quarter_Name],", ")
    )

    VAR _Q1 =
    SUMMARIZE(
            FILTER(
                ALL('Calendar_Table'),
                'Calendar_Table'[Year] = CurrentYear &&
                'Calendar_Table'[Quarter] = 1
            ),
            'Calendar_Table'[Month Name]

    )

    VAR Check_Q1 =
    IF(
        COUNTROWS(
        INTERSECT(SelectedMonths,_Q1)
    ) = 3,
    "Q1"
    )  

    VAR _Q2 =
    SUMMARIZE(
            FILTER(
                ALL('Calendar_Table'),
                'Calendar_Table'[Year] = CurrentYear &&
                'Calendar_Table'[Quarter] = 2
            ),
            'Calendar_Table'[Month Name]

    )

    VAR Check_Q2 =
    IF(
        COUNTROWS(
        INTERSECT(SelectedMonths,_Q2)
    ) = 3,
    "Q2"
    )

    VAR _Q3 =
    SUMMARIZE(
            FILTER(
                ALL('Calendar_Table'),
                'Calendar_Table'[Year] = CurrentYear &&
                'Calendar_Table'[Quarter] = 3
            ),
            'Calendar_Table'[Month Name]

    )

    VAR Check_Q3 =
    IF(
        COUNTROWS(
        INTERSECT(SelectedMonths,_Q3)
    ) = 3,
    "Q3"
    )

    VAR _Q4 =
    SUMMARIZE(
            FILTER(
                ALL('Calendar_Table'),
                'Calendar_Table'[Year] = CurrentYear &&
                'Calendar_Table'[Quarter] = 4
            ),
            'Calendar_Table'[Month Name]

    )

    VAR Check_Q4 =
    IF(
        COUNTROWS(
        INTERSECT(SelectedMonths,_Q4)
    ) = 3,
    "Q4"
    )

    VAR _MorethanHalf =
    SWITCH(
        TRUE(),
        COUNTROWS(INTERSECT(SelectedMonths,_Half1)) = 6,"H1 & "&CONCATENATEX(EXCEPT(SelectedMonths,_Half1),'Calendar_Table'[Month Name],", "),
        COUNTROWS(INTERSECT(SelectedMonths,_Half2)) = 6,CONCATENATEX(EXCEPT(SelectedMonths,_Half2),'Calendar_Table'[Month Name],", ")&" & H2",
        NOT(ISBLANK(Check_Q1)) && NOT(ISBLANK(Check_Q3)),"Q1 & Q3 & "&CONCATENATEX(EXCEPT(SelectedMonths,UNION(_Q1,_Q3)),'Calendar_Table'[Month Name],", "),
        NOT(ISBLANK(Check_Q1)) && NOT(ISBLANK(Check_Q4)),"Q1 & Q4 & "&CONCATENATEX(EXCEPT(SelectedMonths,UNION(_Q1,_Q4)),'Calendar_Table'[Month Name],", "),
        NOT(ISBLANK(Check_Q2)) && NOT(ISBLANK(Check_Q3)),"Q2 & Q3 & "&CONCATENATEX(EXCEPT(SelectedMonths,UNION(_Q2,_Q3)),'Calendar_Table'[Month Name],", "),
        NOT(ISBLANK(Check_Q2)) && NOT(ISBLANK(Check_Q4)),"Q2 & Q4 & "&CONCATENATEX(EXCEPT(SelectedMonths,UNION(_Q2,_Q4)),'Calendar_Table'[Month Name],", ")
    )





    VAR QTitle =
    SWITCH(
        TRUE(),
        NOT(ISBLANK(Check_Q1)), Check_Q1,
        NOT(ISBLANK(Check_Q2)), Check_Q2,
        NOT(ISBLANK(Check_Q3)), Check_Q3,
        NOT(ISBLANK(Check_Q4)), Check_Q4,
        BLANK()
    )

    VAR QTitleH1 =
    SWITCH(
        TRUE(),
        NOT(ISBLANK(Check_Q1)), Check_Q1,
        NOT(ISBLANK(Check_Q2)), Check_Q2,
        BLANK()
    )

    VAR QTitleH2 =
    SWITCH(
        TRUE(),
        NOT(ISBLANK(Check_Q3)), Check_Q3,
        NOT(ISBLANK(Check_Q4)), Check_Q4,
        BLANK()
    )

    VAR _9Months =
    IF(
        COUNTROWS(
        INTERSECT(SelectedMonths,_Half1)
        ) = 6,
        "H1 & "&QTitleH2,
        QTitleH1&" & H2"
    )

    VAR _EXCEPTQ1 = EXCEPT(SelectedMonths,UNION(_Half2,_Q2))
    VAR _EXCEPTQ2 = EXCEPT(SelectedMonths,UNION(_Half2,_Q1))
    VAR _EXCEPTQ3 = EXCEPT(SelectedMonths,UNION(_Half1,_Q4))
    VAR _EXCEPTQ4 = EXCEPT(SelectedMonths,UNION(_Half1,_Q3))

    VAR _Morethan9Months =
    SWITCH(
        TRUE(),
        COUNTROWS(INTERSECT(SelectedMonths,_Half1)) = 6 && COUNTROWS(_EXCEPTQ3)>0,
        "H1 & "&Check_Q3&" & "&CONCATENATEX(EXCEPT(SelectedMonths,UNION(_Half1,_Q3)),'000 445_Calendar_Table'[Month Name],", "),
        COUNTROWS(INTERSECT(SelectedMonths,_Half1)) = 6 && COUNTROWS(_EXCEPTQ4)>0,
        "H1 & "&Check_Q4&" & "&CONCATENATEX(EXCEPT(SelectedMonths,UNION(_Half1,_Q4)),'Calendar_Table'[Month Name],", "),
        COUNTROWS(INTERSECT(SelectedMonths,_Half2)) = 6 && COUNTROWS(_EXCEPTQ1)>0,
        CONCATENATEX(EXCEPT(SelectedMonths,UNION(_Half2,_Q1)),'Calendar_Table'[Month Name],", ")&" & "&Check_Q1&" & H2",
        COUNTROWS(INTERSECT(SelectedMonths,_Half2)) = 6 && COUNTROWS(_EXCEPTQ2)>0,
        CONCATENATEX(EXCEPT(SelectedMonths,UNION(_Half2,_Q2)),'Calendar_Table'[Month Name],", ")&" & "&Check_Q2&" & H2",
        BLANK()
    )

    VAR _QandMonths =
    SWITCH(
        TRUE(),
        NOT(ISBLANK(Check_Q1)),Check_Q1&" & "&CONCATENATEX(EXCEPT(SelectedMonths,_Q1),'Calendar_Table'[Month Name],", "),
        NOT(ISBLANK(Check_Q2)),Check_Q2&" & "&CONCATENATEX(EXCEPT(SelectedMonths,_Q2),'Calendar_Table'[Month Name],", "),
        NOT(ISBLANK(Check_Q3)),Check_Q3&" & "&CONCATENATEX(EXCEPT(SelectedMonths,_Q3),'Calendar_Table'[Month Name],", "),
        NOT(ISBLANK(Check_Q4)),Check_Q4&" & "&CONCATENATEX(EXCEPT(SelectedMonths,_Q4),'Calendar_Table'[Month Name],", ")
    )

    VAR Result =
    SWITCH(
        TRUE(),
        _CM = 12,"Total Year",
        _CM>9, _Morethan9Months,
        _CM = 9, _9Months,
        _CM>6 && _CM<9, _MorethanHalf,
        _CM = 6,HalfTitle,
        _CM>3 && _CM<6, _QandMonths,
        _CM = 3,QTitle,
        CONCATENATEX(SelectedMonths,'Calendar_Table'[Month Name],", ")
       
    )


    RETURN
    Result
     
    If anyone has a better solution please enlighten me!
    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hello there, Please I have a small tweak to the solution you have here. So for mine it is just 2slicers for Quarter (Q1, Q2, Q3, Q4) and Month (Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec) and the user can make multiple selections. I don't seem to understand how to adjust the DAX you have to suit for mine. Can you help me with that? Thank you