Custom String List - Slicer

[BSimmers]

Hi - Looking for some guidance to create a custom list to use in a slicer: Day on Day, Week On Week, Month On Month. When selected, a table will be ordered (filtered) Top N by that dimension. i.e. Top 10 by Day On Day Change Pct.

Any help on how to go about this would be great.

Thanks,

Ben.

[Anonymous]

Hi @BSimmers ,

To create a custom slicer based on the selected dimension (Day on Day, Week on Week, Month on Month) and use that slicer to filter and sort the data in the table to display the top N in order of the selected dimension (e.g. top 10 in order of percentage change in Day on Day), you can follow these steps
First, you need to create a reference table containing the options you want to display in the slicer (Day on Day, Week on Week, Month on Month).

SlicerTable = 
DATATABLE(
    “Period”, STRING.
    {{“Day on Day”}, {“Week on Week”}, {“Month on Month”}}
)

This table will contain a column “Period” with values “Day on Day”, “Week on Week” and ” Month on Month”.
2. Create metrics to calculate the percentage change:
You need to create DAX metrics for Day on Day, Week on Week and Month on Month changes. Each metric should calculate the percentage change based on the selected period.

DayOnDayChangePct = 
VAR CurrentDayValue = SUM('YourData'[ValueColumn])
VAR PreviousDayValue = 
  CALCULATE(SUM('YourData'[ValueColumn]), DATEADD('YourData'[ValueColumn]), VAR 
  DATEADD('YourData'[DateColumn], -1, DAY))
RETURN
   IF(PreviousDayValue = 0, BLANK(), (CurrentDayValue - PreviousDayValue) / PreviousDayValue)

WeekOnWeekChangePct = 
VAR CurrentWeekValue = SUM('YourData'[ValueColumn])
VAR PreviousWeekValue = 
    CALCULATE(SUM('YourData'[ValueColumn]), 
    DATEADD('YourData'[DateColumn], -1, WEEK))
RETURN
    IF(PreviousWeekValue = 0, BLANK(), (CurrentWeekValue - PreviousWeekValue) / PreviousWeekValue)

MonthOnMonthChangePct = 
VAR CurrentMonthValue = SUM('YourData'[ValueColumn])
VAR PreviousMonthValue = 
    CALCULATE(SUM('YourData'[ValueColumn]), DATEADD('YourData'[ValueColumn]), VAR PreviousMonthValue = VAR 
    DATEADD('YourData'[DateColumn], -1, MONTH))
RETURN
    IF(PreviousMonthValue = 0, BLANK(), (CurrentMonthValue - PreviousMonthValue) / PreviousMonthValue)

3. Create dynamic metrics based on slicer selection:
In order for the table to dynamically display data based on slicer selections (Day on Day, Week on Week, Month on Month), you need a DAX metric that returns a corresponding percentage change metric based on the selected period.

SelectedChangePct = 
SWITCH(
    TRUE(),
    SELECTEDVALUE(SlicerTable[Period]) = “Day on Day”, [DayOnDayChangePct],
    SELECTEDVALUE(SlicerTable[Period]) = “Week on Week”, [WeekOnWeekChangePct],
    SELECTEDVALUE(SlicerTable[Period]) = “Month on Month”, [MonthOnMonthChangePct],
    BLANK()
)

This metric returns the corresponding percent change metric based on the slicer selection.
4. Creating Top N Filters
In order to display Top N based on the selected period (e.g. to display Top 10 percentage change of Day on Day), you can use the `TOPN` function in the table visualization.

Top10Table = 
VAR TopNValue = 10
RETURN
    TOPN(
        
        
        [SelectedChangePct], 
        DESC
    )

This Top10Table will return the top 10 rows of data based on the value of the selected change percentage metric.
5. visualization settings:
Add the SlicerTable[Period]column to the slicer.
Add your data table (or Top10Table`) to the table visualization.
Use the SelectedChangePct` metric to sort your table by the selected change percentage (Day on Day, Week on Week, Month on Month).

In this way you can dynamically filter and sort the data in your table according to the slicer selection.

Hope it helps!

Best regards,
Community Support Team_ Tom Shen

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.