cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mkumetat Frequent Visitor
Frequent Visitor

Filter/DAX function to count occurrence of words

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:

 

Table data.PNG

 

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 Smiley Happy

 

Best,

Marvin

1 ACCEPTED SOLUTION

Accepted Solutions
OwenAuger Super Contributor
Super Contributor

Re: Filter/DAX function to count occurrence of words

Hi Marvin,

 

I have attached a sample PBIX.

 

I would suggest setting up the data model as follows:

image.png

  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:image.png

     

  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.image.png

     

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

 

Regards,

Owen

 



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




1 REPLY 1
OwenAuger Super Contributor
Super Contributor

Re: Filter/DAX function to count occurrence of words

Hi Marvin,

 

I have attached a sample PBIX.

 

I would suggest setting up the data model as follows:

image.png

  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:image.png

     

  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.image.png

     

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

 

Regards,

Owen

 



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Helpful resources

Announcements
Community Highlights

Community Highlights

Find out what's new in the Power BI Community!

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 316 members 3,196 guests
Please welcome our newest community members: