Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello, I am new on Power BI and faces a difficult problem, thank you for your help!
Context: Website users can subscribe to groups on this website.
In my database, I have a table "Subscriptions", with registers, for each subscription, the date of subscription and the user_id.
I would like to create a graph which displays, for each day, the number of users which are in 0 group, 1 group, 2 groups and 3 groups.
I hope it's more clear with the joined picture...
How can I do this? Do I need to use a calendar table?
Thanks a lot for your support,
Jean-Sebastien
In this scenario, you can create a calculated table to aggregate number of groups first.
Table 2 = SUMMARIZE ( subscription, subscription[Create_at], subscription[user_id], "number of groups", COUNT ( subscription[group_id] ) )
Then you can use above table to create your chart, just put the user_id in "Value" box and change the aggregate type into "Count".
Regards,
Thank you, it's a first step, but I would like also to display, for each day, how many users are in no group, in 1 group, in 2 groups, in 3 groups... I would like to obtain this table as result (see below picture).
And the subscriptions are cumulative from a day to another.
Thank you very much if you can help me!
Hi @jeanseb
In your most recent example, you say "subscriptions are cumulative from a day to another". Please let me know if this is the data you would then want plotted (based on your sample data)
Date 0 groups 1 group 2 groups 3 groups 2017-01-01 9 1 0 0 2017-01-02 16 3 1 0 2017-01-03 23 5 2 0 2017-01-04 29 8 2 1 2017-01-05 35 11 2 2 etc
Hi @dedelman_clng ,
by cumulative, I mean that if a user subscribes to one group at 2017/01/01 and subscribes to three groups at 2017/01/02, then on 2017/01/03, he will be member of 4 groups.
Below you can see a picture which shows my inputs (table "Subscriptions to groups") and the output I want (the graph), with two intermediate steps (table 2 and table 3).
Thanks for the help!
I'm afraid I'm stuck on this one. I keep getting close, but not quite there.
First I created a calendar table and made a relationship between the subscription table and the date table.
Then I made a summary table:
Summary = SUMMARIZE ( Subs, Subs[Created_Dt], Subs[UsserID], "NumGroups", CALCULATE ( COUNTA ( Subs[GroupID] ), FILTER ( ALL ( Subs[Created_Dt] ), Subs[Created_Dt] <= MAX ( DateTab[Date] ) ) ) )
I then created a relationship between the Summary table and a Calendar table.
Then measures for 1, 2 and 3 groups (that again are close, but need more work).
Num1 = VAR NG = CALCULATE ( COUNTA ( Summary[NumGroups] ), FILTER ( ALL ( DateTab[Date] ), DateTab[Date] <= MAX ( DateTab[Date] ) ), Summary[NumGroups] = 1 ) RETURN IF ( ISBLANK ( NG ), 0, NG )
(change 1s to 2s and 3s for other measures) I can't get a Num0 measure to work because of all of the "blanks" in the summary table. I'm hoping someone can take my work and fix/complete it.
David
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |