Forum Discussion
PowerBI via SharePoint reporting distinct entries within Lookup and Choice fields
I have a SharePoint list with several fields in it, one is a lookup to another SharePoint list and another is a Choice field, with the values coded into the list itself. Both allow multiple values to be selected.
When I try to build a PowerBI report on these fields via PowerBI connected to SharePoint, I cannot separate these multiple list options to calculate the correct count of items.
When I build the report via PowerBI Desktop then I can use PowerQuery to get the right values and create multiple rows to get the right details into the page.
Example: I'm tracking meetings in a list, the topics people discuss and the attendees. The attendees are colleagues looked up from another sharepoint list and the topics are the choice field.
Bob attends a meeting with a client and talks about Automobiles and Pharmaceutical targets (1 attendee, 2 topics)
Bob and Karen attend a meeting with a different client to talk about Automobiles only (2 attendees, 1 topic).
I need PowerBI to show that Bob has attended two meetings and Karen one. I want to show that Automobiles were discussed twice and Pharmaceuticals once.
I can do this in Power BI Desktop but not in PowerBI via SharePoint. When I report via Sharepoint then Bob attends one meeting and Bob+Karen attends another meeting. Similar, Pharamecuticals are discussed in one meeting and Pharamceuticals+Automobiles are discussed in one meeting. I want to get a a distinct count of the people and topics, not combined.
Thanks for any help you can offer!
3 Replies
Hi David0802,
Can you please share some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test.
How to Get Your Question Answered Quickly
Regards,
Xiaoxin Sheng
Thanks Anonymous . Hopefully the below is useful, I'm new to the community and not sure if this is the best way to share sample, dummy data.
- The Attendee column is a lookup to another SharePoint list with People's names in them.
- The Topic column is a Choice column.
- Both columns can store one or more values.
Meeting ID Client Attendee (SP List lookup) Topics (SP Choice) 1 Client A David, Bob Cars, Planes 2 Client B Margaret Planes 3 Client C Bob Cars 4 Client A David, Bob Cars, Planes 5 Client D Bob Trucks 6 Client C David, Bob, Margaret Cars In the (bar chart) reports I'd expect to see:
Attendees
- Bob - 5
- David - 3
- Margaret - 2
Topics
- Cars - 4
- Planes - 3
- Trucks - 1
But what i currently see is
Attendees
- David, Bob - 2
- Bob - 2
- Margaret - 1
- David, Bob, Margaret - 1
Topics
- Cars, Planes - 2
- Cars - 2
- Planes - 1
- Trucks - 1
Thanks again.
HI David0802,
I'd like to suggest you create two calculated tables to extract and expand the value from these two fields, then you can write measure formulas to calculate corresponding items.
Calculated tables:
Attendee Table = VAR _path = SUBSTITUTE ( CONCATENATEX ( VALUES ( 'Table'[Attendee (SP List lookup)] ), [Attendee (SP List lookup)], "," ), ",", "|" ) VAR _length = PATHLENGTH ( _path ) RETURN DISTINCT ( SELECTCOLUMNS ( GENERATESERIES ( 1, _length, 1 ), "Attendee", TRIM ( PATHITEM ( _path, [Value] ) ) ) ) Topics Table = VAR _path = SUBSTITUTE ( CONCATENATEX ( VALUES ( 'Table'[Topics (SP Choice)] ), [Topics (SP Choice)] , "," ), ",", "|" ) VAR _length = PATHLENGTH ( _path ) RETURN DISTINCT ( SELECTCOLUMNS ( GENERATESERIES ( 1, _length, 1 ), "Topic", TRIM ( PATHITEM ( _path, [Value] ) ) ) )Measure formulas:
AttendeeCount = VAR currAttendee = SELECTEDVALUE ( 'Attendee Table'[Attendee] ) RETURN COUNTROWS ( FILTER ( 'Table', SEARCH ( currAttendee, 'Table'[Attendee (SP List lookup)], 1, -1 ) > 0 ) ) TopicCount = VAR currTopic = SELECTEDVALUE ( 'Topics Table'[Topic]) RETURN COUNTROWS ( FILTER ( 'Table', SEARCH ( currTopic, 'Table'[Topics (SP Choice)], 1, -1 ) > 0 ) )Result:
Regards,
Xiaoxin Sheng
