topic Re: Get selected values from Hierarchy respecting Hierarchy Level in DAX Commands and Tips

Get selected values from Hierarchy respecting Hierarchy Level

zoberender — Thu, 19 Oct 2023 20:55:05 GMT

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

Re: Get selected values from Hierarchy respecting Hierarchy Level

AlexisOlson — Thu, 19 Oct 2023 22:44:33 GMT

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:

Re: Get selected values from Hierarchy respecting Hierarchy Level

zoberender — Fri, 20 Oct 2023 15:43:48 GMT

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" )

Re: Get selected values from Hierarchy respecting Hierarchy Level

AlexisOlson — Mon, 13 Nov 2023 17:43:10 GMT

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.

Re: Get selected values from Hierarchy respecting Hierarchy Level

zoberender — Fri, 20 Oct 2023 16:36:50 GMT

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