Count of people attending a meeting

[Anonymous]

I am looking to count the amount of people attending meetings by Enternal and Interanl. See example of the sort of data I am working with below. 

Meeting	External People	Internal People
Meeting 1	Josh Fish	Dave Bolton
Meeting 2	Donald Driver, Dave Cat	Dave Bolton
Meeting 3	Josh Fish, Max Power, Fred Smith	John Smith
Meetign 4	None	John Smith, Dave Bolton

[Anonymous]

Here's the M code for Power Query to transform the data into a normalized form:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8k1NLcnMS1cwVNJR8sovzlBwyyzOOLQAyHNJLEtVcMrPKcnPA/JjdRBqjSDy+XmJOSkKLkWZZalFOgpg5c6JJQT0GiPbo6Pgm1ihEJBfDjLArSg1RSE4N7MkA6wkIw/KgetOz1MwAUr55eeloqiA2g2xTik2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Meeting = _t, #"External People" = _t, #"Internal People" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Meeting", type text}, {"External People", type text}, {"Internal People", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Meeting"}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([Value] <> "None")),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Filtered Rows", {{"Value", Splitter.SplitTextByDelimiter(",", QuoteStyle.None), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Value"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Value", type text}}),
    #"Trimmed Text" = Table.TransformColumns(#"Changed Type1",{{"Value", Text.Trim, type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Trimmed Text",{{"Attribute", "Attendee Type"}, {"Value", "Attendee"}})
in
    #"Renamed Columns"

Just paste this into the Advance Editor in PQ and execute. You'll see how this is done. Then create the measures I've already told you about.

Best

D

[Anonymous]

Jup Darloves solution is the better one, mine is just trying to work around a sub-optimal table, but his goes to the root of the issue and fixes it. Do what he suggests!

[Anonymous]

How about this solution posted by someone else? You basically count the number of comma's +1 and you have the number of people attending:

https://community.powerbi.com/t5/Desktop/Count-no-of-comma-occurrences-in-a-column/td-p/79183

[Anonymous]

Thanks. I tried this but meetings where there are "None" External people it comes up with 1 rather than 0. 

[Anonymous]

Ah yes, so then you write something like this instead of +1:

+
IF( internal_people = BLANK(),0,1)

[Anonymous]

The I can't seem to add the IF function to an Meaured column and refrnce the column. And the formular from the link doesn't work in a new column it come up with the right number half the time. See example of code used below. 

=
LEN(Internal_People)-LEN(SUBSTITUTE(Internal_People,",",""))+IF(Internal_People=BLANK(),0,1)

[Anonymous]

The data format for this is wrong. Good models store one piece of info in a cell, not many of them. Please use Power Query to normalize the data first and then your measures will be very easy to write.

It'll be as easy as

[People Count] = DISTINCTCOUNT( T[Person] )

[External People Count] =
CALCULATE(
	[People Count],
	KEEPFILTERS( T[Person Type] = "External" )
)

[Internal People Count] =
CALCULATE(
	[People Count],
	KEEPFILTERS( T[Person Type] = "Internal" )
)

Best

D