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
- Created a measure
ForecastCategoryCountto count the distinct values of forecast source per forecast category.
ForecastCategoryCount = DISTINCTCOUNT('forecast_filters'[Forecast Type])
- 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.
2
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 using None: