Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

Screen Shot 2020-01-10 at 6.45.36 PM.png

1 ACCEPTED SOLUTION
Ashish_Mathur
Super User
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
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

4 REPLIES 4
Ashish_Mathur
Super User
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
https://www.linkedin.com/in/excelenthusiasts/

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
Super User
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")

 

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
Super User
Super User

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

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.