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
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.
Solved! Go to 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
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
@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!
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
@patkang ,
countx(distinct(union(all(Table[UserID]), all(TableB[UserID]))),[UserID])
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
110 | |
99 | |
95 | |
88 | |
70 |
User | Count |
---|---|
165 | |
131 | |
129 | |
102 | |
98 |