Need help on filtering multiple values of same columns using And

[sushma1412_Ind]

This is my data .

Here i want  to find out count of employees who has done both Power BI and Azure.

As per data only Empid =1 is the only employee who has done both certifications and count should be one and that count i want to use in other calculations.

[sushma1412_Ind · Accepted solution]

Here is the solution .create a measure as below will give results

ABC =

var aTT = SUMMARIZE(

FILTER(ALL(Emp),Emp[Certification]="Power BI"),Emp[Empid],Emp[Certification])

var bTT = SUMMARIZE(

FILTER(ALL(Emp),Emp[Certification]="Azure"),Emp[Empid],Emp[Certification])

return

COUNTROWS(NATURALINNERJOIN(SELECTCOLUMNS(aTT,"EmployeeID",Emp[Empid]),SELECTCOLUMNS(bTT,"EmployeeID",Emp[Empid])))

[Fowmy]

@sushma1412_Ind 

You can create a the following measure

Count of Emp =
CALCULATE (
    DISTINCTCOUNT ( tablename(empid),
    tablename(certification) IN {"Power BI" , "Azure"} )
)

[sushma1412_Ind]

@Fowmy - This will work as OR condition and it will give the count of employees who has done power bi or Azure and we will get count as 2 but i want count as 1 who has done both Powerbi and Azure

[Fowmy]

@sushma1412_Ind 

The formula I shared should give you one as the result as I have used DISTINCTCOUNT.

If you use AND then you wont any results as a single row cannot contain both so please test it and reply

[sushma1412_Ind]

any suggestions