topic Re: Count of people attending a meeting in DAX Commands and Tips

Count of people attending a meeting

Anonymous — Wed, 25 Mar 2020 12:57:11 GMT

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

Re: Count of people attending a meeting

Anonymous — Wed, 25 Mar 2020 13:39:06 GMT

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

Re: Count of people attending a meeting

Anonymous — Wed, 25 Mar 2020 14:17:00 GMT

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

Re: Count of people attending a meeting

Anonymous — Wed, 25 Mar 2020 16:04:39 GMT

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

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

Re: Count of people attending a meeting

Anonymous — Wed, 25 Mar 2020 18:42:38 GMT

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)

Re: Count of people attending a meeting

Anonymous — Wed, 25 Mar 2020 19:49:05 GMT

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

Re: Count of people attending a meeting

Anonymous — Wed, 25 Mar 2020 19:59:08 GMT

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

Re: Count of people attending a meeting

Anonymous — Thu, 26 Mar 2020 09:04:24 GMT

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!