Measure or Calculated Column for filtering specific condition and comparing to original table

[Anonymous]

Here is made up data to show what I'm trying to accomplish:

TraineeId	CertificationID
234	9
234
234	21
234	15
234	21
234	11
567	21
567
567	9
567	11
678	4
789	13
789	71

 Each row signifies the number of courses taken for each trainee id (in my actual data, I have courseId but excluded it for simplicity). So for example traineeID 234 has taken 6 courses, so and so forth. I am trying to create a calculated column (or measure),  called ' Certification 11 Certified't thats whether or not the individual has the certification 11. The only two values in the column should be "Yes" or "No." 

Previously, I've created a calculated column that IF Certification ID = 11, then Yes, ELSE "No." I applied this calculated column to a card measure, and when I slice by trainee ID, I realized that this calculation results in ambiguity because it the column will have the "No" value for the trainee ID 234, when the certification ID states 9 for that individual, even though the the trainee ID 234 in the sixth row has the certification ID = 11. 

I also tried creating a measure using calculate table and filter and I also ran into issue, the function didn't compile. So question is how would I go about showing trainee id has certification 11, despite the way my data is modeled? Should I create a virtual table with all the trainee ids and then create another table that filters for certification ID = 11, then compare the IDs in the second table with the first table and then only select rows in which the trainee id is found in the filtered table? I tried doing this with calculatedtable function and filter and was having trouble. I'm trying to determine the solution for this inquiry. Thanks for your time.

[Ashish_Mathur · Accepted solution]

Hi,

Drag TraineeID to the row labels of your visual and write this measure

=IF(CALCULATE(COUNTROWS(Data),Data[CertificationID]="11")>0,"Yes","No")

Hope this helps.

[NikoMendez]

Hi..

I agree with @Ashish_Mathur  (I was just posting the same formula 😉 ) and I think that is the one to use.

I'd just add, that you can use it as Calculated column or measure. If you need to use a slicer with YES/NO selection create a calculated column, otherwise, create a measure..

[amitchandak]

On the same table, you can lookup and flag

Cert 11 column = if(ISBLANK(LOOKUPVALUE(Sheet1[TraineeId],Sheet1[CertificationID],11,Sheet1[TraineeId],FIRSTNONBLANK(Sheet1[TraineeId],TRUE()),firstnonblank(Sheet1[TraineeId],true()))),"No","Yes")

Link : https://www.dropbox.com/s/e447c2jnwp54p95/certificate.pbix?dl=0

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-wi...
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[HotChilli]

You could have a table to hold Trainee data , for example, DimTrainee = DISTINCT(TableCourses[TraineeID])

TableCourses is your ESGTrainingCoursesOnline

and you could add a column to show certification 11,

for example,

Cert11 = if (LOOKUPVALUE(TableCourses[CertificationID], TableCourses[TraineeId], DimTrainee[TraineeId], TableCourses[CertificationID], 11) = 11, "Yes", "No")