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:

       

      UserID
      Name
      1Brian
      2Heather
      3Clayton
      4Greg
      5Jennifer

       

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

       

       

      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.

       

      Thanks!