Formatting data with live connection - create tables using functions similar to countifs

[ctupps]

Hello,

I'm new to BI and am trying to figure out the best option for formatting data from a live survey database. I have not found exactly what I'm looking for in the training topics or forum, so I am hoping for some direction on how to approach this task.

Let's imagine my survey is asking which flavors of ice cream people have purchased in the past month. The flavors question is multi-select, and the results populate with multiple text strings separated by spaces. People may submit multiple entries. The example database looks like this:

Survey Dataset 1

name     flavors

Bob        chocolate

Omar     strawberry vanilla

Omar     chocolate mint chip vanilla

Alisha     strawberry butter pecan

Bob        chocolate vanilla

Alisha     vanilla

Omar      mint chip

What I need is a new table which counts the instances of each flavor purchased distinctly for each person. The resulting table for this example should look like this:

Table 1

name      butter pecan    chocolate     mint chip    strawberry    vanilla

Bob                                        2                                                        1

Omar                                     1                   2                  1                2

Alisha              1                                                             1                1

In Excel, I would create a formula for each flavor nesting COUNTIFS with CONTAINS, instructing the first cell to display the number of rows where name="Bob" and flavors contains "butter pecan", for example. 

I'd also like to create a second table counting the total number of (discrete) people who purchased each flavor. It should look like this: 

Table 2 

flavor                    number of people

butter pecan         1

chocolate              2

mint chip              1

strawberry            2

vanilla                   3

For this table, I could easily create a formula referencing Table 1 to count the number of cells >0 under each flavor column.

For both tables, I need them to account for any new rows added when the data connection is refreshed. I am unsure of whether this is best accomplished using the Power Query function, or by creating new tables using DAX commands, or something else. In either case, I need instructions on what steps to take and what DAX commands to use to do this. 

Thank you very much in advance for your help!

[tamerj1 · Accepted solution]

Hi @ctupps 
Here is a sample file with the solution https://www.dropbox.com/t/gPKV4ZNVUhjVHqxY

You can use power query to automatically append new datasets with master dataset. Create a list of all unique "flavors" and use the following measures

Frequency = 
IF (
    HASONEVALUE ( Flavors[Flavor] ),
    COUNTROWS ( 
        FILTER ( 
            Dtatset,
            CONTAINSSTRING ( Dtatset[flavors], SELECTEDVALUE ( Flavors[Flavor] ) )
        )
    )
)

No. of People = 
IF (
    HASONEVALUE ( Flavors[Flavor] ),
    COUNTROWS ( 
        DISTINCT (
            SELECTCOLUMNS (
                FILTER ( 
                    Dtatset,
                    CONTAINSSTRING (  Dtatset[flavors], SELECTEDVALUE ( Flavors[Flavor] ) ) 
                ),
                "@Name", Dtatset[name]
            )
        )
    )
)

[ctupps]

Wonderful. Thank you so much!