cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Anonymous
Not applicable

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

1 ACCEPTED SOLUTION
Super User

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.

Regards,
Ashish Mathur
http://www.ashishmathur.com
4 REPLIES 4
Super User

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.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Frequent Visitor

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

Super User

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")``

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

Super User

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

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")``

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors