Forum Discussion

David0802's avatar
David0802
Frequent Visitor
4 years ago

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...
Need Help
David0802's avatar
David0802
Frequent Visitor
4 years ago

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 IDClientAttendee (SP List lookup)Topics (SP Choice)
1Client ADavid, BobCars, Planes
2Client BMargaretPlanes
3Client CBobCars
4Client ADavid, BobCars, Planes
5Client DBobTrucks
6Client CDavid, Bob, MargaretCars

 

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. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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