Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
52 | |
27 | |
24 | |
13 | |
9 |
User | Count |
---|---|
74 | |
58 | |
47 | |
16 | |
12 |