powerbidaxpowerbi-custom-visuals

Limiting a Slicer's Multi-Select Options by A Category


I have a forecast report coming from different sources where each source belongs to 1 of 2 possible categories (Customer Forecast or Partner Forecast). In order to keep the report organized I placed a slicer which allows the viewer to select the source(s) of the forecast they want to see.

Ideally the user should not select more than one forecast source belonging to the same category, but I would like to enforce this via a rule/measure without limiting the slicer to a "Single Select" option.

e.g. If Forecasts A and E belong to "Category A" and Forecasts X and Y belong to "Category B" I can multi-select Forecasts A or B with X or Y but never Forecasts A and B or Forecasts X and Y.

Forecast Source Forecast Category Combinable With
Forecast A Customer Forecast X and Y
Forecast B Customer Forecast X and Y
Forecast X Partner Forecast A and B
Forecast Y Partner Forecast A and B

What I Did

  1. Created a measure ForecastCategoryCount to count the distinct values of forecast source per forecast category.
ForecastCategoryCount = DISTINCTCOUNT('forecast_filters'[Forecast Type])
  1. I placed a filter on the slicer to only allow the ForecastCategoryCount <= 1

What Happened

The result has no effect on the multi-selection slicer. I assume that it is so because the slicer's field is the Forecast Type so the result of the formula will always be 1.

** Update **

I created a measure that calculates the total count of unique values (# of forecasts sources per forecast category) based on the selected values in a specific column, considering any other filters applied to the data.

ForecastCounts = 
CALCULATE(
    DISTINCTCOUNT('forecast_filters'[Forecast Source]),
    ALLSELECTED('forecast_filters'[Forecast Source])
)

While the calculation works, when I filter ForecastCategoryCount <= 1 the slicer completely stops working.

Data as requested

The slicer takes a value from a table (forecast_filters) created specifically as a related table to filter across other tables below is a sample of the data in that table where I create the measures I have displayed:

Origin Destination Forecast Source Forecast Type
100023 400012 Forecast A Customer Forecast
100040 400080 Forecast A Customer Forecast
100023 400012 Forecast B Customer Forecast
100040 400080 Forecast B Customer Forecast
200083 900080 Forecast X Partner Forecast
200099 900055 Forecast Y Partner Forecast

The slicer filter should not allow me to select both A and B not both X and Y.


Solution

  • Since there are only 2 possible categories that a user can select, I recommend creating 2 tables for each Category and their Sources.

    Slicer Cat 1 Calculated Table:

    Slicer Cat 1 = 
    VAR Category = "Customer Forecast"
    
    VAR Source =  SELECTCOLUMNS(FILTER('Table', [Forecast Category] = Category)
                    , "Source", [Forecast Source]
                    )
    RETURN UNION(Source
                , ROW("Source", "None")
    )
    

    Slicer Cat 2 Calculated Table:

    Slicer Cat 2 = 
    VAR Category = "Partner Forecast"
    
    VAR Source =  SELECTCOLUMNS(FILTER('Table', [Forecast Category] = Category)
                    , "Source", [Forecast Source]
                    )
    
    RETURN UNION(Source
                , ROW("Source", "None")
    )
    

    Each table adds a None option to allow the user not to select a value.

    Then create the following measure to filter visuals by the selected slicer:

    ShowRows = 
     
     VAR thisVal = MAXX('Table', [Forecast Source])
     VAR slicerSelect1 = SELECTEDVALUE('Slicer Cat 1'[Source])
     VAR slicerSelect2 = SELECTEDVALUE('Slicer Cat 2'[Source])
    
     VAR output = IF(
                    ISEMPTY(
                        FILTER('Table'
                            , [Forecast Source] = slicerSelect1 
                            || [Forecast Source] = slicerSelect2
                        )
                    ), 0 , 1
     )
    
     RETURN output
    

    Then for the desired visual, add the ShowRows measure to the Filters on this visual. Set the filter to is 1. Then it will remove all other data for the visual.

    Example

    Example using None:

    None Example