Forum Discussion

gab2020's avatar
gab2020
Icon for Microsoft Employee rankMicrosoft Employee
6 years ago
Solved

Multiple Slicers from Multiple Columns

Hi,

 

I have a table built as following in Power BI.

 

How to create 2 slicers to filter the table as follows:

IdentityNo. Slicer: A slicer with values from IdentityNo. 1, IdentityNo. 2 and IdentityNo. 3.

E.g. selecting 9232039 from the slicer will filter the table and output the entire 2nd row.

 

IdentityCountry Slicer: A slicer with values from IdentityCountry 1, IdentityCountry 2, IdentityCountry 3?

E.g. selecting MY from the slicer will filter the table and output the entire 2nd and 3rd rows.

 

How to create a card to do a distinct count of values from IdentityCountry 1, IdentityCountry 2, IdentityCountry 3?

E.g. Card of UK will show a total of 3 since UK appears 3 times in the 2nd, 3rd, 4th rows.

 

Thanks. 🙂

Need Help
  • v-alq-msft's avatar
    v-alq-msft
    6 years ago

    Hi, gab2020 

     

    Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end. 

    Table:

     

    Slicer No(a calculated table):

    Slicer No = 
FILTER(
    DISTINCT(
        UNION(
            DISTINCT('Table'[IdentityNo.1]),
            DISTINCT('Table'[IdentityNo.2]),
            DISTINCT('Table'[IdentityNo.3])
        )
    ),
    [IdentityNo.1]<>BLANK()
)

     

    Slicer Country:

    Slicer Country = 
FILTER(
    DISTINCT(
        UNION(
            DISTINCT('Table'[IdentityCountry 1]),
            DISTINCT('Table'[IdentityCountry 2]),
            DISTINCT('Table'[IdentityCountry 3])
        )
    ),
    [IdentityCountry 1]<>BLANK()
)

     

    You may create two measures as below.

    Visual Control = 
IF(
    AND(
        SELECTEDVALUE('Table'[IdentityNo.1]) in DISTINCT('Slicer No'[IdentityNo])||
        SELECTEDVALUE('Table'[IdentityNo.2]) in DISTINCT('Slicer No'[IdentityNo])||
        SELECTEDVALUE('Table'[IdentityNo.3]) in DISTINCT('Slicer No'[IdentityNo]),
        SELECTEDVALUE('Table'[IdentityCountry 1]) in DISTINCT('Slicer Country'[IdentityCountry])||
        SELECTEDVALUE('Table'[IdentityCountry 2]) in DISTINCT('Slicer Country'[IdentityCountry])||
        SELECTEDVALUE('Table'[IdentityCountry 3]) in DISTINCT('Slicer Country'[IdentityCountry])
    ),
    1,0
)

Count UK = 
var tab = 
ADDCOLUMNS(
    'Table',
    "flag",
    IF(
        AND(
            'Table'[IdentityNo.1] in DISTINCT('Slicer No'[IdentityNo])||
            'Table'[IdentityNo.2] in DISTINCT('Slicer No'[IdentityNo])||
            'Table'[IdentityNo.3] in DISTINCT('Slicer No'[IdentityNo]),
            'Table'[IdentityCountry 1] in DISTINCT('Slicer Country'[IdentityCountry])||
            'Table'[IdentityCountry 2] in DISTINCT('Slicer Country'[IdentityCountry])||
            'Table'[IdentityCountry 3] in DISTINCT('Slicer Country'[IdentityCountry])
        ),
        1,0
    )
)
return
COUNTROWS(
    FILTER(
        tab,
        [flag]=1&&
        [IdentityCountry 1]="UK"
    )
)+
COUNTROWS(
    FILTER(
        tab,
        [flag]=1&&
        [IdentityCountry 2]="UK"
    )
)+
COUNTROWS(
    FILTER(
        tab,
        [flag]=1&&
        [IdentityCountry 3]="UK"
    )
)

     

    Finally you may put 'Visual Control' measure in the visual level filter and use 'IdentityNo', 'IdentityCountry' from 'Slicer No', 'Slicer Country' to filter the result.

     

    Best Regards

    Allan

     

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

     

8 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    gab2020 , create independent slicer , do not join with any of the countries. Or use cross join to remove join

     

    Then try like

    measure =
    var _max = selectedvalue(Country[country])
    return
    calculate(count(Table[identity No]) , filter(Table , Table[IdentityCountry 1] = _max && Table[IdentityCountry 2] = _max && Table[IdentityCountry 3] = _max ))

     

    measure =
    var _max = allselected(Country[country]) // or use // values(Country[country])
    return
    calculate(count(Table[identity No]) , filter(Table , Table[IdentityCountry 1] in _max && Table[IdentityCountry 2] in _max && Table[IdentityCountry 3] in _max ))

     

    You can do same for another slicer too

     

    there is an example of cross filter here

    https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

     

  • v-alq-msft's avatar
    v-alq-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi, gab2020 

     

    Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end. 

    Table:

     

    Slicer No(a calculated table):

    Slicer No = 
FILTER(
    DISTINCT(
        UNION(
            DISTINCT('Table'[IdentityNo.1]),
            DISTINCT('Table'[IdentityNo.2]),
            DISTINCT('Table'[IdentityNo.3])
        )
    ),
    [IdentityNo.1]<>BLANK()
)

     

    Slicer Country:

    Slicer Country = 
FILTER(
    DISTINCT(
        UNION(
            DISTINCT('Table'[IdentityCountry 1]),
            DISTINCT('Table'[IdentityCountry 2]),
            DISTINCT('Table'[IdentityCountry 3])
        )
    ),
    [IdentityCountry 1]<>BLANK()
)

     

    You may create two measures as below.

    Visual Control = 
IF(
    AND(
        SELECTEDVALUE('Table'[IdentityNo.1]) in DISTINCT('Slicer No'[IdentityNo])||
        SELECTEDVALUE('Table'[IdentityNo.2]) in DISTINCT('Slicer No'[IdentityNo])||
        SELECTEDVALUE('Table'[IdentityNo.3]) in DISTINCT('Slicer No'[IdentityNo]),
        SELECTEDVALUE('Table'[IdentityCountry 1]) in DISTINCT('Slicer Country'[IdentityCountry])||
        SELECTEDVALUE('Table'[IdentityCountry 2]) in DISTINCT('Slicer Country'[IdentityCountry])||
        SELECTEDVALUE('Table'[IdentityCountry 3]) in DISTINCT('Slicer Country'[IdentityCountry])
    ),
    1,0
)

Count UK = 
var tab = 
ADDCOLUMNS(
    'Table',
    "flag",
    IF(
        AND(
            'Table'[IdentityNo.1] in DISTINCT('Slicer No'[IdentityNo])||
            'Table'[IdentityNo.2] in DISTINCT('Slicer No'[IdentityNo])||
            'Table'[IdentityNo.3] in DISTINCT('Slicer No'[IdentityNo]),
            'Table'[IdentityCountry 1] in DISTINCT('Slicer Country'[IdentityCountry])||
            'Table'[IdentityCountry 2] in DISTINCT('Slicer Country'[IdentityCountry])||
            'Table'[IdentityCountry 3] in DISTINCT('Slicer Country'[IdentityCountry])
        ),
        1,0
    )
)
return
COUNTROWS(
    FILTER(
        tab,
        [flag]=1&&
        [IdentityCountry 1]="UK"
    )
)+
COUNTROWS(
    FILTER(
        tab,
        [flag]=1&&
        [IdentityCountry 2]="UK"
    )
)+
COUNTROWS(
    FILTER(
        tab,
        [flag]=1&&
        [IdentityCountry 3]="UK"
    )
)

     

    Finally you may put 'Visual Control' measure in the visual level filter and use 'IdentityNo', 'IdentityCountry' from 'Slicer No', 'Slicer Country' to filter the result.

     

    Best Regards

    Allan

     

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