Counting responses in rows - multiple text strings needs to be ignored

[ddw3]

Hi Forum

I need to count rows with a measure where there are some "regular", and some "mixed" responses to a survey all stuck in one column. I need to count the columns where "other" responses where provided.

For simplicity, lets call the regular responses A, B and C.

All regular responses A's, B's and C's are always the same, so A might be = "yes", B = "no", C = "don't know", as an example.
And lets call all other responses X. X can be anything. Like - X = "this is a stupid survey" or X = "why are we doing this"

So the data might be as follows in my Survey_Column:
row 1 : A; B; C
row 2 : A; B
row 3 : X
row 4 : X; A; B

I've tried:

CALCULATE(
COUNTROWS('Table'),
SEARCH("A",'Table'[Survey_Column],1,-1)=-1,
SEARCH("B",'Table'[Survey_Column],1,-1)=-1,
SEARCH("C",'Table'[Survey_Column],1,-1)=-1
)

This returns a count of 1, since all the rows with A, B and C has been left out. But I need the count to be 2, since there are two rows where other responses were provided?

[AlB · Accepted solution]

Hi @ddw3 

This is easiest done in Power Query. You can create an additional column that will tell you if "other" answers were provided. Then you can operate on that column very easily in DAX. Place the following M code in a blank query to see the steps.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcrRWcLJWcFaK1YGwwYwICGmtABGJBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),

    validResponses_ = {"A", "B", "C"}, 
    #"Added Custom" = Table.AddColumn(Source, "Other responses provided?", each if List.Count(List.Difference(Text.Split([Column1],"; "), validResponses_)) >0 then true else false, type logical)
in
    #"Added Custom"

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[wdx223_Daniel · Accepted solution]

@ddw3 PowerQuery is the better selection. if you insist resolve it in DAX,please refer the below code

[AlB · Accepted solution]

Hi @ddw3 

This is easiest done in Power Query. You can create an additional column that will tell you if "other" answers were provided. Then you can operate on that column very easily in DAX. Place the following M code in a blank query to see the steps.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcrRWcLJWcFaK1YGwwYwICGmtABGJBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),

    validResponses_ = {"A", "B", "C"}, 
    #"Added Custom" = Table.AddColumn(Source, "Other responses provided?", each if List.Count(List.Difference(Text.Split([Column1],"; "), validResponses_)) >0 then true else false, type logical)
in
    #"Added Custom"

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers