Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
patkang
Helper I
Helper I

Calculate Distinctcount of userid with two tables

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  
UserIDNameLogins
11

John

2
12Sarah3
13Eric1
14Mike2

 

Table B  
UserIDDateItemType
1109/15/20Video
1309/16/20Book
1309/17/20Simulation
1119/18/20Video

 

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.

1 ACCEPTED SOLUTION
AlB
Super User
Super User

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 

 

SU18_powerbi_badge

View solution in original post

4 REPLIES 4
AlB
Super User
Super User

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 

 

SU18_powerbi_badge

@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
Not applicable

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
Super User
Super User

@patkang ,

 

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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.