Search code examples
powerbidaxcalculated-columns

One count DAX for multiple columns that contains same values but in unorganised order, connected to rows

Hopefully I can make myself understood, we are doing a research on NPS (Customer satisfaction from 1 to 10 (1 sad and 10 happy) ). We use Quicksearch and have exported the raw data to PowerBI.

Every customer that has answered can choose up to 14 things they are satisfied/not satisfied with. I will name this A, B, C, D, E, F, G, H, I, J, K, L, M, N but in our report all the different letters are sentences, so: A = "Booking procedure" B = "Our prices" C = "Our website" and so on.

My goal is to get a record count of how many times each letter (sentences) has been mentioned (count) and thereafter I can filter away satisfied/not satisfied into different charts.

An example could look like this:

NPS Why satisfied/not satisfied
6 A, B, C
5 C, D, A, J
1 B, C, A,
7 N, A, K, J
10 H, I, N, A, B
4 K
3 A, B, C, D, E, F, K, L
6 K, L, D, E, A, B

I've delimiter all the letters by comma, so now it is "Why satisfied/not satisfied" up to #6 (six columns).

NPS Why satisfied/not satisfied 1 Why satisfied/not satisfied 2 Why satisfied/not satisfied 3
6 A B C
5 C D A
1 B C A
7 N A K

E.t.c.

And if i try to separate all the letters and put it in the same column, I will loose the possibility to filter out depending on what NPS (1-10) the customer choose. Because if they answer 10 in NPS and say "Our prices" they are satisfied and vice versa.

Each row (every answer) is over 30 cells long, so I can't lose that data that's why I somehow need to count/merge these "why satisfied..." columns so I can visualise it in example a bar chart.

Thanks in advance

Solution

  • You can create a Calculated Column with the following DAX Formula without the need of splitting the columns

    DAX: Calculated Column

    Count Responses = 
    PATHLENGTH(
        SUBSTITUTE([Why satisfied/not satisfied],",","|")
    )

    Output

    enter image description here

    Edit

    I have created a table containing all the letters of the alphabet with this DAX Code

    Letters = 
ADDCOLUMNS ( GENERATESERIES ( 1, 26, 1 ), "Letter", UNICHAR ( [Value] + 64 ) )

    It creates this table

    enter image description here

    With that table created, I create the following measure

    DAX Measure

    CountLetters =
VAR SelectedLetter =
    SELECTEDVALUE ( Letters[Letter] )
VAR t1 =
    ADDCOLUMNS (
        'Table',
        "LetterBoolean", CONTAINSSTRING ( [Why satisfied/not satisfied], SelectedLetter )
    )
RETURN
    COUNTROWS ( FILTER ( T1, [LetterBoolean] = TRUE ) )

    Output

    Using the Letter column from the Letters table and CountLetters measure the output is:

    enter image description here