Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Limit slicer based on multiple slicers

Hi, I've built 4 identical slicers each with different types of cancer a person can have across their lifetime. The full list has 10 cancer types and would like subsequent slicers to only have the o...
  • PaulDBrown's avatar
    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

     

     

  • Anonymous's avatar
    Anonymous
    4 years ago

    This is brilliant. Just bedded this into my dashboard and works a treat. Many thanks Paul.