Calculate Distinctcount of userid with two tables

[patkang]

Dear Community

I have a simple request, which I believe is not too difficult but i cant seem to find a solution

For an online platform I am trying to find out the active vs inactive users but need to know their userID as well, not just calculating the difference of the total number of users. 

There are two tables in a 1 to many relationship, connected via the userID

Table A: shows the summary of all usersID, name and number of logins. A user will get a record as soon as he/she logs in. Each user has one userID.

Table B: shows the activity of users. Each time someone watches a video or looks at a reading will get an entry, but not all users have accessed an item, so some users in Table A won't appear in Table B

An example below

Table A
UserID	Name	Logins
11	John	2
12	Sarah	3
13	Eric	1
14	Mike	2

Table B
UserID	Date	ItemType
11	09/15/20	Video
13	09/16/20	Book
13	09/17/20	Simulation
11	19/18/20	Video

So I would like a count of the userID of all users in Table A but excluding the ones who are in Table B if that makes sense. 

In this example it would be User 12 and 14, therefore a count of 2. 

Any help is appreciated, do i need to make a calculated table? Thanks a lot.

[AlB · Accepted solution]

Hi @patkang 

You could do it with a calculated table, yes

New table =
EXCEPT ( DISTINCT ( TableA[Id] ), DISTINCT ( TableB[Id] ) )

or you could also do it with a table visual, which would be dynamic

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[patkang]

@AlB thanks a lot, I throught it may be solvable without a calculated table. Thinking again, this really solved my problem. Appreciate your support and thanks to all who replie! You guys rock!

[Anonymous]

You could do a merge of Table A with Table B 

Go to the transform editor.

Click Table A

Click Merge

Select both tables

Select Left outer join

Select The columns on which should be merged

Perform the merge

Expand the columns from Table B

Delete all columns of Table B except the UserId

If you do a count on the new column of all blank values the you the number

Below an example i did with a table 1 containing values 1 2 3 4

And table contaning values 2 3 5

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSK1YlWMgKTxmDSRCk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}}),
#"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Column1"}, Table, {"Column1"}, "Table", JoinKind.LeftOuter),
#"Expanded Table" = Table.ExpandTableColumn(#"Merged Queries", "Table", {"Column1"}, {"Table.Column1"}),
Table2 = #"Expanded Table"{0}[Table]
in
Table2

[amitchandak]

@patkang ,

countx(distinct(union(all(Table[UserID]), all(TableB[UserID]))),[UserID])