Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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.
Thanks @AlexisOlson ! This gets me really close. I've been trying to tweak a few things this morning.
Added:
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"
)
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.
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
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:
User | Count |
---|---|
57 | |
22 | |
21 | |
19 | |
16 |
User | Count |
---|---|
86 | |
84 | |
52 | |
37 | |
23 |