Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I have two tables:
games:
game_created: MM/DD/YYYY
users:
user_created: MM/DD/YYYY
I would like to create two meassures:
1. Old Users = COUNT users that where created 7 days before game_created, or: count(user_id) where user_created + 7 days > game_created.
2. New Users = COUNT users that where created 6 days or less than game_created, or: count(user_id) where user_created + 6 days < game.created.
Any help will be appreciated!
Thanks!
Solved! Go to Solution.
Actually I solved it this way:
Jugadores =
SELECTCOLUMNS(
FILTER(NATURALINNERJOIN(users,game_entries),[private]=0&&[entry_currency]="real"),
"user id",
users[id],
"date created",
INT(FORMAT(users[created],"YYYYmm")),
"date game",
FORMAT(game_entries[_game_date],"YYYY-mm"),
"difference",
INT(FORMAT(game_entries[_game_date],"YYYYmm")) - INT(FORMAT(users[created],"YYYYmm")),
"new",
IF(INT(FORMAT(game_entries[_game_date],"YYYYmm")) - INT(FORMAT(users[created],"YYYYmm")) =0,game_entries[user_id],BLANK()),
"old",
IF(INT(FORMAT(game_entries[_game_date],"YYYYmm")) - INT(FORMAT(users[created],"YYYYmm")) >0,game_entries[user_id],BLANK()),
"game id",
game_entries[game_id]
)
Try this:
Create a collumn in the Game Table with the sintaxe:
Actually I solved it this way:
Jugadores =
SELECTCOLUMNS(
FILTER(NATURALINNERJOIN(users,game_entries),[private]=0&&[entry_currency]="real"),
"user id",
users[id],
"date created",
INT(FORMAT(users[created],"YYYYmm")),
"date game",
FORMAT(game_entries[_game_date],"YYYY-mm"),
"difference",
INT(FORMAT(game_entries[_game_date],"YYYYmm")) - INT(FORMAT(users[created],"YYYYmm")),
"new",
IF(INT(FORMAT(game_entries[_game_date],"YYYYmm")) - INT(FORMAT(users[created],"YYYYmm")) =0,game_entries[user_id],BLANK()),
"old",
IF(INT(FORMAT(game_entries[_game_date],"YYYYmm")) - INT(FORMAT(users[created],"YYYYmm")) >0,game_entries[user_id],BLANK()),
"game id",
game_entries[game_id]
)
potentially you have multiple games and multiple users
what is the field (or fields) that links users to games
Help when you know. Ask when you don't!
That is correct.
A user can play several games.
I link them with the user id.
Games:
game_id
game.user_id
game.date_created
Users:
user_id
user.created
What Im trying to visualize in a table is:
Total users that played games in a certain month
Total new users that played games in a certain month (users created in that month)
Total old users that played games in a certain month (users created in past months)
your first visualization can be done by just adding the fields user / game to a matrix and use a count of usergamesplayed id to count the games
the second visualization will be to get the date the user was created from the user table, you can do this by using the Related() function which will give you access to the user record for any usergameplayed, and then to get the date created from the game table. you can then compare the 2 dates and if they are the same its a new user.
You can use the same measure but when you compare if the dates are not the same its an old user, since if a user plays a game in a month they either where created in that month, or in some previous month
Help when you know. Ask when you don't!
I assume you also have a table UserGamePlayed,
with user id, game id and date
that would be your fact table
user table, with user id and date created would be a dimension in a 1 to many relationship to user id in UserGamePlayed
Game table with game id and date created would also be a dimension
Help when you know. Ask when you don't!
Yup! The problem is to come up with the syntax of the query haha...
you should add a calendar table to be a dimension that you could link to the date in UserGamesPlayed, and one for user and game tables as well....so you don't have to calculated the month for the date, but can get it from the calendar table
Help when you know. Ask when you don't!
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |