How can I use a filter whose selections are cumulative (AND) and not distinct (OR)?

[Lejouquard]

Hi guys,

In my example I have a table with resources and their language skills (French, English, German, etc).

When I select 2 or more languages with a filter I would like to show only the resources who speak the 2 or more selected languages.

For example, if I select French & German I just want to show the resources who have the two skills : Danish AND German (in yellow) but not the resources who speak Danish OR German.

How I can do that ?

Thank you for your help!

[mahoneypat · Accepted solution]

Here is one potential way to do this.  You can make a table visual with just the Employee Name column and this measure.  Employees that don't have all selected languages would return blank and not show in the visual.

Speaks All Selected Languages =
VAR __selected =
    COUNTROWS (
        ALLSELECTED ( Table[Language] )
    )
RETURN
    IF (
        DISTINCTCOUNT ( Table[Language] ) = __selected,
        "Y",
        BLANK ()
    )

Regards,

Pat

[Lejouquard]

Thx Pat, that's very helpful!

Last question :

If there is no selection in the filter, I would like to show all the employee name.

How i can do that?

Many thanks

[v-yiruan-msft]

Hi @Lejouquard ,

Please try to update the formula of measure which @mahoneypat  wrote as below and check if it can get your expected result:

Speaks All Selected Languages =
VAR __selected =
    COUNTROWS ( ALLSELECTED ( 'Table'[Year] ) )
VAR _allcount =
    CALCULATE ( DISTINCTCOUNT ( 'Table'[Year] ) )
RETURN
    IF (
        DISTINCTCOUNT ( 'Table'[Year] ) = __selected
            || HASONEVALUE ( 'Table'[Year] )
            || __selected = _allcount,
        "Y",
        BLANK ()
    )

Best Regards
Rena
Community Support Team _ Rena Ruan
If this post helps, then please consider Accept it as the solution to help the other members find it more.