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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.