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,
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".
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!
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.
We're excited to announce our first cross-community 'Can You Solve These?' challenge!
Visit our Community Blog for articles, guides, and information created by fellow community members.
Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.
We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.