Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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

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.

  • 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.

4 Replies