Get selected values from Hierarchy respecting Hierarchy Level

[zoberender]

Hi all, 

I'm struggling on this issue.  I'd like to create a visual that shows which slicers are available.  Specifically, I have 2 slicers that are part of hierarchies that are being used.  The slicer on the date hierarchy uses 'Year Number' & 'Month Short Name' from my dim_Date table.  I also have a BU hierarchy that has BU Level 1, BU Level 2 and BU Level 3 from my dim_Account table.  What I'd really like to create is a visual that shows which slicers are applied.  I've been able to accomplish this for each of the individual fields, but they all get lumped together.  I know i need to combine these in some way to keep the levels in tact, but I can't for the life of me figure it out.  

When there is no filter selected, I'd like to see something like:

ALL Years: All Months

If however the user selected Jan 2023, May & June 2022 and ALL of 2021

In Card form:

Dates Selected = 

2023: Jan

2022: May, June

2021: ALL Months

In table form:

Year	Month
2023	Jan
2022	May June
2021	ALL Months

I'd also want to limit it to 3-4 results and return an etc (or something) if there are more selected.  And if there are no filters I don't want to show all of the years, I just want it to say ALL Years (or ALL Months), etc.  

Below is the code I have for the Selected Year.  Same type of measure exists for Selected Month and the BU Levels as well.  

Selected Year = 
VAR __DISTINCT_VALUES_COUNT = DISTINCTCOUNT('dim_Date'[Year Number])
VAR __MAX_VALUES_TO_SHOW = 3
RETURN
IF(ISFILTERED(dim_Date[Year Number]),
	IF(
		__DISTINCT_VALUES_COUNT > __MAX_VALUES_TO_SHOW,
		CONCATENATE(
			CONCATENATEX(
				TOPN(
					__MAX_VALUES_TO_SHOW,
					VALUES('dim_Date'[Year Number]),
					'dim_Date'[Year Number],
					ASC
				),
				'dim_Date'[Year Number],
				", ",
				'dim_Date'[Year Number],
				ASC
			),
			", etc."
		),
		CONCATENATEX(
			VALUES('dim_Date'[Year Number]),
			'dim_Date'[Year Number],
			", ",
			'dim_Date'[Year Number],
			ASC
		)
	), 
"ALL Years")

 I also have a measure to combine Year and Month, that I hoped would have kept the correct year/month pairings together.  But alas it does not.  With my existing measures, what I get is:
Selected Year & Month:

2021, 2022, 2023: ALL MONTHS

Selected Year & Month = [Selected Year] & ": " &[Selected Month]

I'm confident if I can get it working with the date, I can adapt it to work for the BU data as well.  

Hoping someone can help.  Thanks!
Zac

[zoberender]

Thanks @AlexisOlson !  This gets me really close.  I've been trying to tweak a few things this morning. 

Added:

• IF statement to the __ExtraString.  If there were only 2 year selected it would give a "+ - 1 year(s)" at the end.  
• IF( ISFILTERED statement to the RETURN string as I just wanted it to say "ALL Dates" if no filters are applied.  
• Changed dim_Date[Month Short Name] to dim_Date[Month Number] in the CONCATENATEX to sort the months correctly.  

I think there is one piece left that I just can't get solved.  I should have mentioned this in my original question, but overlooked it.  My date table runs from 1/1/19 - 8/31/23.  What I can't solve is for the current year/month combination.  The measure you provided works as expected for 2022 and prior, but for 2023 there are only 8 months in the date table for the year.  For 2023, if I select the entire year (8 months), I would like to get back "ALL".  What I get back is 2023 Jan, Feb, Mar +5.  I know it has to do with the __MonthsInYear and __MonthCount, but haven't been able to get a solution figured out this morning. 

With all 8 months of 2023 selected, I expected to see "ALL" for 2023.   

Date Selection = 
VAR __N = 3
VAR __FullSelection = SUMMARIZE ( dim_Date, dim_Date[Year Number], dim_Date[Month Name], dim_Date[Month Short Name], dim_Date[Month Number] ) 
VAR __Years = DISTINCT ( SELECTCOLUMNS ( __FullSelection, "dim_Date[Year Number]", dim_Date[Year Number] ) ) 
VAR __MonthsInYear = COUNTROWS( ALL(dim_Date[Month Name]) )
VAR __TopNYears = TOPN ( __N, __Years, dim_Date[Year Number], DESC ) 
VAR __ExtraYears = COUNTROWS ( __Years ) - __N 
VAR __ExtraString = IF(__ExtraYears > 0, " +" & __ExtraYears & " year(s)", "")
VAR __Delim1 = UNICHAR(10) & "     "
VAR __Delim2 = UNICHAR(10) 
RETURN 
    IF(ISFILTERED(dim_Date[Year Number]), 
        CONCATENATEX ( 
            __TopNYears, 
            VAR __CurrYear = dim_Date[Year Number] 
            VAR __CurrSelection = FILTER ( __FullSelection, dim_Date[Year Number] = __CurrYear ) 
            VAR __MonthCount = COUNTROWS ( __CurrSelection )
            VAR __ExtraMonths = __MonthCount - __N
            VAR __TopNMonths = TOPN ( __N, __CurrSelection , dim_Date[Month Number], ASC ) 
            VAR __Concat = 
                CONCATENATEX ( 
                    __TopNMonths, 
                    dim_Date[Month Short Name], 
                    __Delim1, 
                    dim_Date[Month Number], ASC ) 
            VAR __MonthText = 
            SWITCH ( 
                TRUE(), 
                __MonthCount = __MonthsInYear, "ALL", 
                __MonthCount > __N, __Concat & __Delim1 & "+" & __ExtraMonths, 
                __Concat ) 
            RETURN  
                dim_Date[Year Number] & " " & __MonthText, 
                __Delim2, 
                dim_Date[Year Number], DESC ) & __ExtraString, 
            "ALL Dates"
            )

[AlexisOlson · Accepted solution]

I think you can move __MonthsInYear inside the outer CONCATENATX to resolve your remaining issue.

Date Selection = 
VAR __N = 3
VAR __FullTable = SUMMARIZE ( ALL ( dim_Date ), dim_Date[Year Number], dim_Date[Month Short Name], dim_Date[Month Number] ) 
VAR __FullSelection = SUMMARIZE ( dim_Date, dim_Date[Year Number], dim_Date[Month Short Name], dim_Date[Month Number] ) 
VAR __Years = DISTINCT ( SELECTCOLUMNS ( __FullSelection, "dim_Date[Year Number]", dim_Date[Year Number] ) )
VAR __TopNYears = TOPN ( __N, __Years, dim_Date[Year Number], DESC ) 
VAR __ExtraYears = COUNTROWS ( __Years ) - __N 
VAR __ExtraString = IF(__ExtraYears > 0, " +" & __ExtraYears & " year(s)", "")
VAR __Delim1 = UNICHAR(10) & "     "
VAR __Delim2 = UNICHAR(10) 
RETURN 
    IF(ISFILTERED(dim_Date[Year Number]), 
        CONCATENATEX ( 
            __TopNYears, 
            VAR __CurrYear = dim_Date[Year Number]
            VAR __FullCurrYear = FILTER ( __FullTable, dim_Date[Year Number] = __CurrYear )
            VAR __CurrSelection = FILTER ( __FullSelection, dim_Date[Year Number] = __CurrYear )
            VAR __MonthsInYear = COUNTROWS ( __FullCurrYear )
            VAR __MonthCount = COUNTROWS ( __CurrSelection )
            VAR __ExtraMonths = __MonthCount - __N
            VAR __TopNMonths = TOPN ( __N, __CurrSelection , dim_Date[Month Number], ASC ) 
            VAR __Concat = 
                CONCATENATEX ( 
                    __TopNMonths, 
                    dim_Date[Month Short Name], 
                    __Delim1, 
                    dim_Date[Month Number], ASC ) 
            VAR __MonthText = 
            SWITCH ( 
                TRUE(), 
                __MonthCount = __MonthsInYear, "ALL", 
                __MonthCount > __N, __Concat & __Delim1 & "+" & __ExtraMonths, 
                __Concat ) 
            RETURN  
                dim_Date[Year Number] & " " & __MonthText, 
                __Delim2, 
                dim_Date[Year Number], DESC ) & __ExtraString, 
            "ALL Dates"
            )

Edit: I've attached a sample pbix.

[zoberender]

Thanks again @AlexisOlson.  I think that did it for me on the Date side.  I may continue to tweak the delimeter's to get it to a place that's visually what I want. 

I am going to go back and try to modify the code for the Business Unit selection.  This one may be a little more complicated as there are 3 levels (BU 1, 2 and 3) versus 2 levels for the Date (Year, Month).  

Hopefully I can take what you've provided as a template and apply it further!  I'll reach out if I run into any issues.  Thanks again!

Zac

[AlexisOlson]

I don't know if this is exactly what you need but it should get you closer and give you a starting place to tinker with:

DateSelection = 
VAR __N = 3
VAR __FullSelection = SUMMARIZE ( Dates, Dates[Year], Dates[Month], Dates[MonthShort] )
VAR __Years = DISTINCT ( SELECTCOLUMNS ( __FullSelection, "Dates[Year]", Dates[Year] ) )
VAR __MonthsInYear = COUNTROWS ( ALL ( Dates[Month] ) )
VAR __TopNYears = TOPN ( __N, __Years, Dates[Year], DESC )
VAR __ExtraYears = COUNTROWS ( __Years ) - __N
VAR __Delim1 = UNICHAR(10) & "     "
VAR __Delim2 = UNICHAR(10)
VAR __ExtraString = IF ( __ExtraYears > 0, __Delim2 & " +" & __ExtraYears & " year(s)" )
RETURN
    CONCATENATEX (
        __TopNYears,
        VAR __CurrYear = Dates[Year]
        VAR __CurrSelection = FILTER ( __FullSelection, Dates[Year] = __CurrYear )
        VAR __MonthCount = COUNTROWS ( __CurrSelection )
        VAR __ExtraMonths = __MonthCount - __N
        VAR __TopNMonths = TOPN ( __N, __CurrSelection , Dates[Month], ASC )
        VAR __Concat =
            CONCATENATEX (
                __TopNMonths,
                Dates[MonthShort],
                __Delim1,
                Dates[Month], ASC
            )
        VAR __MonthText =
            SWITCH (
                TRUE(),
                __MonthCount = __MonthsInYear, "ALL",
                __MonthCount > __N, __Concat & __Delim1 & "+" & __ExtraMonths,
                __Concat
            )
        RETURN
            Dates[Year] & " " & __MonthText,
        __Delim2,
        Dates[Year], DESC
    ) & __ExtraString

Note: I'm using my own date table. E.g., Date[Year] above corresponds to 'dim_Date'[Year Number] for you.

 
Sample outputs: