list names meet all the filter conditions

[kairoo]

Hello everyone and thanks in advance for your help, this is my first post on the forum.

I have a problem that I can't solve:

Starting from a one table where I have collected the score of each person on a certain Skill:

Name: Professional Name

Competency (ex. PowerBI, Azure, ..) (total 100 competencies)
Value (min= 1 / max = 5)

I need to filter the list of person/s to, for example:

select 2 skills mandatories (ex. Azure & AWS) with the filter and a minimum score value of 5 in all of them, but I get a list of 2 people that each have a 5 in the score or if the same person has a 5, shows it to me twice in the list.

List Filtered =

VAR selectedCompetencies = ALLSELECTED('Professional Skills - ALL Forms Responses FY2023'[Competency])

VAR selectedValues = ALLSELECTED('Professional Skills - ALL Forms Responses FY2023'[Value])

VAR numSelectedCompetencies = COUNTROWS(selectedCompetencies)

RETURN

IF(

    numSelectedCompetencies > 0,

    COUNTX(

        FILTER (

            'Professional Skills - ALL Forms Responses FY2023',

            'Professional Skills - ALL Forms Responses FY2023'[Competency] IN selectedCompetencies &&

            'Professional Skills - ALL Forms Responses FY2023'[Value] IN selectedValues

        ),

        'Professional Skills - ALL Forms Responses FY2023'[Name]

    ),

    IF(

        SELECTEDVALUE('Professional Skills - ALL Forms Responses FY2023'[Value]) = selectedValues,

        CALCULATE(

            COUNTX(

                FILTER (

                    'Professional Skills - ALL Forms Responses FY2023',

                    'Professional Skills - ALL Forms Responses FY2023'[Value] = selectedValues

                ),

                'Professional Skills - ALL Forms Responses FY2023'[Name]

            ),

            ALL('Professional Skills - ALL Forms Responses FY2023')

        ),

        0

    )

)

Example wrong-exit:

the correct output should only show one time Javier because he meets a "5" in the two selected skills, and Tony should not appear because he only meets one.

Thanks !!!!

Kairo

[kairoo]

Hi @ValtteriN 

I appreciate your time and solution, thx.

😉works for a value of "5" perfectly (Skills[Rating]>=5)), thanks !

But if I need to select this Rating with a filter ?, so that it is a 3, 4 or a 5? 

[ValtteriN]

Hi,

Here is one way to do this:

Data:

So here we want to only return Javier for both skills. For AWS everyone except Jane and for Azure Jane and Javier

Dax:

Skill filter =

 var _skillcount = COUNTROWS(ALLSELECTED(Skills[Skill]))

 return

IF(

    COUNTROWS(FILTER(

        Skills, Skills[Rating]>=5))

    >=_skillcount,1,0)

End result (place the measure as a filter so that Skill filter value is 1):

This works as expected. Due to filter context I do not include skills in the table view since that would result in complications, but even in that scenario the base logic of using countrows and if should suffice.

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/