# Conditions from joined tables and measures fail sometimes

**URL:** <https://community.holistics.io/t/conditions-from-joined-tables-and-measures-fail-sometimes/1484>\
**Category:** Ask the Community\
**Tags:** aml, access-control, dashboard\
**Created:** [April 12, 2023, 4:14pm UTC](https://community.holistics.io/t/conditions-from-joined-tables-and-measures-fail-sometimes/1484 "2023-04-12T16:14:46Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![Matt\_Stone](https://community.holistics.io/user_avatar/community.holistics.io/matt_stone/32/1532_2.png) [@Matt\_Stone](https://community.holistics.io/u/Matt_Stone)\
**Post date:** [April 12, 2023, 4:14pm UTC](https://community.holistics.io/t/conditions-from-joined-tables-and-measures-fail-sometimes/1484/1 "2023-04-12T16:14:47Z")

</div>

When I add a filter, one of my measure is breaking wit an error that I don’t understand. Here’s the setup:

We’re reporting on responses to a Net Promoter Score (NPS) quesion on a customer satisfaction survey. We have several clients who get a “client=x” permission on the dashboard when it’s embeded in our app. This approach generally works, but I have one measure that is not cooprating when I use that upstream filter (it’s 2 joins away…)

**simplified relationship model in the dataset:**

 ![CleanShot 2023-04-12 at 08.14.04@2x](https://community.holistics.io/uploads/default/original/2X/6/6256dffe206c7ec73694f5141f6479a7c531f40f.png)

**Model Defintions**  
Answers to NPS Questions, the column `fact_answer_nps.nps_contribution_hidden` is a dimension added in a thequery model as follows:

```auto
select
  q.client,
  q.r_id,
  q.survey_type,
  q.created,
  q.question,
  q.answer::int,
  case
    when q.answer::int between 9 and 10 then 'promoter'
    when q.answer::int between 7 and 8 then 'neutral'
    else 'detractor'
  end as nps_label,
  case
    when q.answer::int between 9 and 10 then 1
    when q.answer::int between 7 and 8 then 0
    else -1
  end as nps_contribution_hidden
from
  {{ #clarus_c_survey_question_answer q }}
  join {{ #clarus_dim_survey_questions t }} on t.question = q.question
where
  t.question_type = 'nps'

```

`fact_answer_nps` is one of several fact tables that is extracted from `clarus_c_survey_question_answer` In this case, I’m casting the answer to an integer for this question type. the column `answer` in `clarus_c_survey_question_answer` is a text or bool for other questions types in other fact tables.

**This model also includes these measure (in AML):**

```measure
    label: "Count"
    type: "number"
    definition: @sql {{ r_id }};;
    description: "Count of NPS answer"
    aggregation_type: "count"
  }

  measure nps_contribution {
    label: "NPS Contribution"
    type: "number"
    definition: @sql {{ nps_contribution_hidden }};;
    aggregation_type: "sum"
    format: "#,###"
  }

  measure nps_score {
    label: "NPS Score"
    type: "number"
    definition: @sql {{ nps_contribution }}::float / {{ count }} * 100;;
    description: "Calculated NPS score"
    aggregation_type: "custom"
    format: "#,###0.00"
  }

```

**Here’s how the problem materializes…**

Create a report that that joins to the client table and and includes ‘nps\_contribution\_hidden’ then, I get the error

```auto
ERROR: invalid input syntax for type integer: ""

```

screen shots of interest:

 ![CleanShot 2023-04-12 at 08.52.06@2x](https://community.holistics.io/uploads/default/original/2X/5/5899ed1031989cc4f2a5e8fe73786ffa63c18fb4.png)  
 ![CleanShot 2023-04-12 at 08.52.52@2x](https://community.holistics.io/uploads/default/original/2X/b/b8758ba187e306af50e2e1d5b9e8e5f6ba149588.png)

---

<div class="post-metadata">

**Author:** ![Toan\_Lam\_Thien](https://community.holistics.io/user_avatar/community.holistics.io/toan_lam_thien/32/1147_2.png) [@Toan\_Lam\_Thien](https://community.holistics.io/u/Toan_Lam_Thien)\
**Post date:** [April 13, 2023, 4:12am UTC](https://community.holistics.io/t/conditions-from-joined-tables-and-measures-fail-sometimes/1484/2 "2023-04-13T04:12:34Z")

</div>

Hi Matt Stone,

Thanks for reaching out to us!

Regarding the issues, I think the dimension `NPS Contribution for measure` contains “” (empty string) and you are trying to cast it into `number` which can cause errors.

For better investigation, it could be very useful if you give us permission to impersonate so that we can figure out what causes this bug.

Looking forward to hearing from you soon.

Bests,

---

<div class="post-metadata">

**Author:** ![Matt\_Stone](https://community.holistics.io/user_avatar/community.holistics.io/matt_stone/32/1532_2.png) [@Matt\_Stone](https://community.holistics.io/u/Matt_Stone)\
**Post date:** [April 13, 2023, 2:12pm UTC](https://community.holistics.io/t/conditions-from-joined-tables-and-measures-fail-sometimes/1484/3 "2023-04-13T14:12:43Z")

</div>

looking for an empty string was a good idea, so I tried this:

```auto
select answer, count(*), sum(answer :: int)
from
  {{ #clarus_c_survey_question_answer q }}
  --{{ #fact_answer_nps q }}
where question ~ 'nps'
group by 1 order by 1

```

the where clause of `question ~ 'nps'` is the logic that collects the underlying data for the fact table.  
I tried the same query on `clarus_c_survey_question_answer` (contains answers to all question types) and `fact_answer_nps` wherein all answers are cast to integers. In both cases the query worked, including the sum aggregation.

the ERROR seems to be related w/ the generated sql (or the way I built up the models in AML) b/c that is a Postgress error that we’re getting.

I can’t share access b/c this is a production system, but I could share the AML. via github.