Contains with multiple columns

Question

hello,

I'm trying to create a calculated field with contains between two columns. The reason I want to do this is: I want to count all rows that have a text, for example, "my text", and save it in a variable, so every row with "my text" will have the same value for the count.

Here's the situation that I have **now**:

| column1 | column2 |
| --- | --- |
| "specific text" | "a text with my text" |
| "specific text" | "a text with nothing" |
| "specific text" | "a text with my text and something" |
| "specific text" | "a text with my text and something else" |
| "specific text" | "a text with nothing else" |

Here's what **I want** to have:

| column1 | column2 | result |
| --- | --- | --- |
| "specific text" | "a text with my text" | 1 |
| "specific text" | "a text with nothing" | 1 |
| "specific text" | "a text with my text and something" | 2 |
| "specific text" | "a text with my text and something else" | 3 |
| "specific text" | "a text with nothing else" | 3 |

So it only increments the result when "my text" is found in column2. For that, I have been struggling to create a calculated field and, so far, what I have done is:

```
countIf(
    {column1},
    contains({column2}, {column1})
)
```

But the following error message keeps appearing: "At least one of the arguments in this function does not have the correct type. Correct the expression and choose 'Create again'."

Please help, I need this ASAP!

Answer

Hello,

In this context, I would like to inform you that I had replicated the scenario with a sample data, in which I have created two columns they are as follows:

Name  |  Status
————————
Ram         Yes
Antony	 No
Jula		 No
Sunny	 Yes
Kell		 Yes
Ryan	 No	
Hansal	 Yes

I first loaded my sample data in QuickSight and created the below countif function. Please find the logic for the same below.

Column name : Count ->	countIf(Name,status=“Yes”)

Once I created the above calculated field, I have added it to the visual and in response, I got the below output. As it shows, If status = yes, then count will be 1.

Name  |  Status |  Count
————————————-
Ram         Yes		1
Antony	 No		0
Jula		 No		0
Sunny	 Yes		1
Kell		 Yes		1
Ryan	 No		0
Hansal	 Yes		1

After that, I have added the show totals, by clicking on menu options with in the visual. So that I have obtained the total of the above count.

Name  |  Status |  Count
————————————-
Ram         Yes		1
Antony	 No		0
Jula		 No		0
Sunny	 Yes		1
Kell		 Yes		1
Ryan	 No		0
Hansal	 Yes		1
		Total   -  4

That being said, to further look into this issue more closely, if you would like to do a resource based troubleshooting, please raise a support case with AWS for further information. If a support case has already been created please be assured that we will get back to you and assist you in the best way possible.

Reference:

[+] https://docs.aws.amazon.com/quicksight/latest/user/countIf-function.html

column1	column2
"specific text"	"a text with my text"
"specific text"	"a text with nothing"
"specific text"	"a text with my text and something"
"specific text"	"a text with my text and something else"
"specific text"	"a text with nothing else"

Contains with multiple columns

Relevant content