2 Replies Latest reply on Apr 14, 2016 1:03 PM by Maddy Pena

    Count if true for two members of the same dimension

    Maddy Pena

      Hello all!

      I apologize in advance if this was asked before. I tried looking for an answer, but I couldn't find anything. My issue is this:

      I have two portals for posting Job Requisitions, one for Internal employees and an External one for everyone who wishes to apply for a certain job. Each requisition has an expiration date for each portal, after that date, the requisition disappears.

       

      So, my data in Tableau is shown like this:

       

      And I would like to count only the req Id's that are actually posted on the two portals, but I don't know how to create the calculate field to do that.

      Thanks in advance!

        • 1. Re: Count if true for two members of the same dimension
          Joshua Milligan

          Maddy,

           

          If you were to right click the Board ID field and change it to a Count Distinct Measure and move it to text, then you'd see the number of Boards that each Job Req ID had been posted to:

           

          01.PNG

           

          So, you can use that in a filter to limit to only the Job Req ID that were posted to two boards.  You could move the CNTD(Board ID) field to filters -- but that would mean the distinct count of Board IDs for all the dimensions in the view (so it works when only Job Req ID is there, but if you add others, the filter won't work the way you want).

           

          So, instead, you can add Job Req ID to the filters and use the Condition tab to tell Tableau to only keep Job Req IDs where the distinct count of Board IDs is 2 (or > 1 or whatever you need specifically):

           

          02.png

           

          Then, you only have Job Req IDs with a 2 count:

           

          03.png

           

          Now, you can count the rows to know how many Job Req IDs had 2 Board IDs (or look in the lower left status indicator for the count).  But if you just want to see the value, you can change Job Req ID on rows to a Measure (specifically a distinct count), so you can know how many Job Req IDs had been posted on 2 boards:

           

          04.png

           

          Hope that helps!

           

          Best Regards,

          Joshua

          • 2. Re: Count if true for two members of the same dimension
            Maddy Pena

            Thank you Joshua!

            This is exactly what I needed , I think I was overcomplicating things trying to do a calculated fields and such.

             

            Best regards and thank you again!