Forum Discussion
Solved
Need help on filtering multiple values of same columns using And
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.
- Here is the solution .create a measure as below will give resultsABC =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])returnCOUNTROWS(NATURALINNERJOIN(SELECTCOLUMNS(aTT,"EmployeeID",Emp[Empid]),SELECTCOLUMNS(bTT,"EmployeeID",Emp[Empid])))
5 Replies
any suggestions
- Fowmy
Super User
sushma1412_Ind
You can create a the following measureCount of Emp = CALCULATE ( DISTINCTCOUNT ( tablename(empid), tablename(certification) IN {"Power BI" , "Azure"} ) )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
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
- Here is the solution .create a measure as below will give resultsABC =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])returnCOUNTROWS(NATURALINNERJOIN(SELECTCOLUMNS(aTT,"EmployeeID",Emp[Empid]),SELECTCOLUMNS(bTT,"EmployeeID",Emp[Empid])))
