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 s...
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!
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