Filter/DAX function to count occurrence of words

[mkumetat]

Hello everyone,

I am an absolute beginner in Power Bi, so please bear with me if my question seems simple. I am trying to visualize the following:

I have a survey where training participants can describe their profession by selecting one or multiple answers. My data looks something like this:

I would like to visualize the second column, for instance in the form of a pie chart. However, I don't know how to make Power BI "filter" this column, so that I would have pie chart with each category ("Videojournalist", "Correspondent", etc.), rather than - as Power BI currently displays it - categories that are named like this: "Videojournalist,Photojournalist, Cameraperson", etc. I know that this means that certain participants would be counted two, three, four times, but that would be okay.

Is there a way to create a DAX function (or pivot table?) to visualize this? Please let me know if you need further information or a sample dataset/pbix file to answer my question.

Many thanks in advance 🙂

Best,

Marvin

[OwenAuger · Accepted solution]

Hi Marvin,

I have attached a sample PBIX.

I would suggest setting up the data model as follows:

1. Participant is a table with one row per Participant, same as the table you posted but with Profession removed.
2. Profession is a table containing distinct values of Profession 
3. ParticipantProfession is a "bridge" table containing the valid combinations of Participant and Profession. I created this using the Split function in Power Query and it looks like this:
4. Relationships are as in diagram above.
   • Participant has 1:* relationship with ParticipantProfession (bidirectional)
   • Profession has 1:* relationship with ParticipantProfession 
5. Create a measure:
   

   Number of Occurrences of Profession = 
   COUNTROWS ( ParticipantProfession )

6. Then you can create visuals that display this measure filtered by Profession[Profession]. Additionally, filtering on a particular Profession will filter the Participant table.

You can see in the attached PBIX how this is set up, but please post back if needed.

Regards,

Owen