topic Re: list names meet all the filter conditions in DAX Commands and Tips

list names meet all the filter conditions

kairoo — Fri, 07 Apr 2023 19:48:58 GMT

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')

)

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

Re: list names meet all the filter conditions

ValtteriN — Fri, 07 Apr 2023 20:15:16 GMT

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/

Re: list names meet all the filter conditions

kairoo — Fri, 07 Apr 2023 22:39:52 GMT

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?