title: How to group records based on a child record in Amazon Quicksight
body: <p>Is there any way to categorise an aggregated record in Amazon Quicksight based on the records being aggregated?</p>
  <p>I'm trying to build a pivot table an Amazon Quicksight to pull out counts of various records split down by what state the record is in limited to a date range where the record first had activity.</p>
  <p>This is all being done in SPICE since the raw data is CSV files in S3. The two CSV files are:</p>
  <p><strong>Student</strong></p>
  <ul>
  <li>Id: String</li>
  <li>Current State: <em>One of</em> <code>pass</code> / <code>fail</code></li>
  <li>Department: String</li>
  </ul>
  <p><strong>Test</strong></p>
  <ul>
  <li>Id: String</li>
  <li>Student Id: String</li>
  <li>DateOfTest: DateTime</li>
  <li>State: <em>One of</em> <code>pass</code> / <code>fail</code></li>
  </ul>
  <p>The pivot table I'm trying to build would have a row for each department and 3 columns:</p>
  <ul>
  <li><strong>fail</strong> - Count of students in the department where the current state is <code>fail</code></li>
  <li><strong>pass-never-failed</strong> - Count of students in the department where the current state is <code>pass</code> and there are no failed tests for the Student</li>
  <li><strong>pass-failed-some</strong> - Count of students in the department where the current state is <code>pass</code> and there is at least one failed test</li>
  </ul>
  <p>To do this I've created a dataset that contains <code>Student</code> joined to <code>Test</code>. I can then create a Pivot table with:</p>
  <ul>
  <li>Row: <code>Department</code></li>
  <li>Column: <code>Current State</code></li>
  <li>Value: <code>Count Distinct(Id[Student])</code></li>
  </ul>
  <p>I thought I'd be able to create a calculated field to give me the three categories and use that in place of <code>Current State</code> using the following calculation:</p>
  <pre><code>ifelse(
      {Current State} = 'pass',
  
      ifelse(
          countIf({Id[Test]}, {State} = 'fail') = 0,
          'pass-never-failed',
          'pass-failed-some'
      ),
  
      {Current State}
  )
  </code></pre>
  <p>But that shows invalid with the following error:</p>
  <pre><code>Mismatched aggregation. Custom aggregations can’t contain both aggregate &quot;`COUNT`&quot; and non-aggregated fields “COUNT(CASE WHEN &quot;State&quot; = _UTF16'fail' THEN &quot;Id[Test]&quot; ELSE NULL END)”, in any combination.
  </code></pre>
  <p>Is there any way to categories the Students based on an aggregation of the Tests in quicksight or do I have to pre-calculate this information in the source data?</p>
  <p>I've been able to workaround this for now by defining three separate calculations for the three columns and adding these as values in the quicksight pivot rather than setting a column dimension at all.</p>
  <p><strong>Count Passed Never Failed</strong></p>
  <pre><code>distinct_countIf({Id[Student]}, maxOver(
      ifelse({State[Test]} = 'fail' AND {Current State[Student]} = 'pass',
          1, 0)
  , [{Id[Student]}], PRE_AGG) = 0)
  </code></pre>
  <p><strong>Count Passed Failed Some</strong></p>
  <pre><code>distinct_countIf({Id[Student]}, maxOver(
      ifelse({State[Test]} = 'fail' AND {Current State[Student]} = 'pass',
          1, 0)
  , [{Id[Student]}], PRE_AGG) &gt; 0)
  </code></pre>
  <p><strong>Count Failed</strong></p>
  <pre><code>distinct_countIf({Id[Student]}, {Current State[Student]} = 'fail')
  </code></pre>
  <p>This works, but I'd still like to know if it's possible to build a dimension that I could use for this as it would be more flexible if new states are added without the special handling of pass.</p>