Forum Discussion
Limit slicer based on multiple slicers
- 4 years ago
For the slicers to be mutually exclusive, you need to create a measures to use in each slicer as a filter in the filter pane, following this (CT Slicer * is the name of each table used as slicers):
Filter 1 = VAR S1 = 'CT Slicer 1' VAR S2 = 'CT Slicer 2' VAR S3 = 'CT Slicer 3' VAR S4 = 'CT Slicer 4' VAR SelectedCT = DISTINCT( UNION(S2, S3, S4)) RETURN COUNTROWS( EXCEPT(S1,SelectedCT))Filter 2 = VAR S1 = 'CT Slicer 1' VAR S2 = 'CT Slicer 2' VAR S3 = 'CT Slicer 3' VAR S4 = 'CT Slicer 4' VAR SelectedCT = DISTINCT( UNION(S1, S3, S4)) RETURN COUNTROWS( EXCEPT(S2, SelectedCT))Filter 3 = VAR S1 = 'CT Slicer 1' VAR S2 = 'CT Slicer 2' VAR S3 = 'CT Slicer 3' VAR S4 = 'CT Slicer 4' VAR SelectedCT = DISTINCT( UNION(S1,S2,S4)) RETURN COUNTROWS( EXCEPT(S3,SelectedCT))Filter 4 = VAR S1 = 'CT Slicer 1' VAR S2 = 'CT Slicer 2' VAR S3 = 'CT Slicer 3' VAR S4 = 'CT Slicer 4' VAR SelectedCT = DISTINCT( UNION(S1,S2,S3)) RETURN COUNTROWS( EXCEPT(S4,SelectedCT))Now add each measure to the filter pane for each corresponding slicer. Before you set the filter, select a different value for each slicer
Then set the value of each measure in each filter to = 1.
I've attached the sample PBIX file
- Anonymous4 years ago
This is brilliant. Just bedded this into my dashboard and works a treat. Many thanks Paul.
For the slicers to be mutually exclusive, you need to create a measures to use in each slicer as a filter in the filter pane, following this (CT Slicer * is the name of each table used as slicers):
Filter 1 =
VAR S1 = 'CT Slicer 1'
VAR S2 = 'CT Slicer 2'
VAR S3 = 'CT Slicer 3'
VAR S4 = 'CT Slicer 4'
VAR SelectedCT =
DISTINCT(
UNION(S2, S3, S4))
RETURN
COUNTROWS(
EXCEPT(S1,SelectedCT))Filter 2 =
VAR S1 = 'CT Slicer 1'
VAR S2 = 'CT Slicer 2'
VAR S3 = 'CT Slicer 3'
VAR S4 = 'CT Slicer 4'
VAR SelectedCT =
DISTINCT(
UNION(S1, S3, S4))
RETURN
COUNTROWS(
EXCEPT(S2, SelectedCT))Filter 3 =
VAR S1 = 'CT Slicer 1'
VAR S2 = 'CT Slicer 2'
VAR S3 = 'CT Slicer 3'
VAR S4 = 'CT Slicer 4'
VAR SelectedCT =
DISTINCT(
UNION(S1,S2,S4))
RETURN
COUNTROWS(
EXCEPT(S3,SelectedCT))Filter 4 =
VAR S1 = 'CT Slicer 1'
VAR S2 = 'CT Slicer 2'
VAR S3 = 'CT Slicer 3'
VAR S4 = 'CT Slicer 4'
VAR SelectedCT =
DISTINCT(
UNION(S1,S2,S3))
RETURN
COUNTROWS(
EXCEPT(S4,SelectedCT))
Now add each measure to the filter pane for each corresponding slicer. Before you set the filter, select a different value for each slicer
Then set the value of each measure in each filter to = 1.
I've attached the sample PBIX file