SUM based on Text Criteria

[ErickRuiz7]

I have a STRING column called Question, this question column provides two possible answeres, either 'Yes; or 'No'

How do I create formulas to sum the amount of times a Yes shows up, the amount of times a No shows up and the amount of times that both of them show up?

[HamzaJ · Accepted solution]

Hey @ErickRuiz7 

You could use a measure filter to achieve this. Let's assume we have 2 columns; question and answer. A formula would look like this:

(Count([Question]), [Answer])

Now you can click on answer and it will show the option Filter. Click on that and you can set the desired filter. This will make the aggregation (in this case a count) count the number of questions for which the answer is , for example, Yes. 

This can be used in more advanced scenario's. In more traditional scenario's you could add Answer as a widget filter. Or add it to the row/column (pivot table) or for example the break-by in a chart. This will automatically list all found values and counts by it

Hamza

[ErickRuiz7]

Thanks for the suggestion, I tried it and partially work, but I forgot to mention that the question can repeat several times, I have a total of 6 different questions and a total of 950 answers to each question, the approach is conting only 6, do you have any suggestion for me?

[TriAnthony]

Hi @ErickRuiz7,

Sounds like you just need to use DUPCOUNT, so the formula should be: (DupCount([Question]), [Answer])

If this still doesn't provide the result you need, could you send example values of all the relevant columns? A screenshot of the desired result would be really helpful as well. Lastly, could you also elaborate on in what kind of situations both Yes and No could show up as an answer and how that is reflected in the database?

[david-h]

Hi Erick, not sure I understand exactly the use case you are on, but it seems that you could use multi pass agregation (kind of adding a group by in an intermediate calculation in your measure). You may also use this old IT trick : yes=1 and no=0. Let us know if this can be useful in your use case, and if not, maybe you can share a siplified data model.

Best,

David.

[ErickRuiz7]

Thanks for answering my question, I did the calculation on the elastic cube using a CASE when, I didn't find a way to do a calculation directly on the dashboard.