1 Reply Latest reply on Aug 24, 2016 10:05 AM by Santiago Sanchez

    Using a one - many relationship - finding if a row is found in the many

    Clayton Chapman

      I understand that my title is a little confusing but that's probably because my problem is a bit confusing.


      I have two tables, one with all of my users, and another with the courses that they have enrolled in. The issue I'm running into is that I need to be able to tell if one of my users has taken and completed a course. In tableau I am trying to do this, but am coming up short.


      Here is what the data looks like:




      Percent Complete
      1CompTIA +100
      1Basic Networking50
      3Basic Networking100
      5Basic Networking60



      So basically, within my 'User' data source I am trying to write a formula that links to the 'Course' data source and checks for a single class - something like: if [Course].[Course] = 'CompTIA+' and [Course].[Percent Complete] = 100 then 1 else 0 end


      But obviously that formula doesn't work because I'm working with aggregates in my secondary data source, it also won't work because some users haven't even enrolled in certain courses. In the end I just want a simple dimension in my User data source that can tell me if they have enrolled and completed a certain course, so I can group them. I am thinking this may have to be some sort of convoluted SQL query, but I wanted to see if there was a way to do this in Tableau that I haven't been able to find yet.