Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I'm hoping someone would be able to help.. Let me give a back drop of how I got to where I am...
I have a form in which users submit their response. One of the form field is multiple selection, so the user can select as many options for the field. I use a PA flow that takes the form response and save it to a SharePoint List.
I use Power BI to source the data from the SharePoint List. This is an example of what the data looks like on the SharePoint List.
Name | State | Attending Event | Event being held | Topics for event |
Joe Doe | New York | Yes | Marathon | ["Running","Networking","CentralPark"] |
Mary Smith | California | Yes | Ultra marathon | ["Big Sur","Desert","Networking"] |
Peter Brown | Illinois | Yes | Marathon | ["Running","Networking","Great Lakes"] |
Humpty Dumpty | New Jersey | Yes | Jersey Shores | ["Beach","Networking","Running"] |
Roger Rabbit | Connecticut | No |
I use the Power BI Split Column (by Row) to get each Topic for event column separate on its own row. When I do that it repeats the data for each of the respondent multiple times. So I end up having Peter Brown on 3 lines and it appears as if Peter Brown filled out the form three times. I deleted the Split Column (by Row) and then used Split Colum (by Column) and it kinda work but I end up having Topics for event in 3 or 4 columns. I'm able to count Peter Brown as Yes just one time but I'm not able to show the number of times Topics for event "Networking" occurred.
I appreciate any help and hoping I make sense.
Solved! Go to Solution.
@Anonymous - I really think you'll be better off by using the data like:
I am not really sure how you're trying to visualize the data however you can use something like:
Count of Submissions = DISTINCTCOUNT(TableName[Name])
to get something like:
see attached.
Proud to be a Super User!
@ChrisMendoza- Just earlier, I started out using DistinctCount and got distracted. I'm going back to this and will let you know how it works. Appreciate you looking at this and responding!
@Anonymous - I really think you'll be better off by using the data like:
I am not really sure how you're trying to visualize the data however you can use something like:
Count of Submissions = DISTINCTCOUNT(TableName[Name])
to get something like:
see attached.
Proud to be a Super User!
User | Count |
---|---|
98 | |
90 | |
84 | |
71 | |
67 |
User | Count |
---|---|
114 | |
103 | |
100 | |
72 | |
64 |