Dax calculations with Power BI

[jeanseb]

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

[v-sihou-msft]

@jeanseb

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,

[jeanseb]

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! 

[dedelman_clng]

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

[jeanseb]

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!

[dedelman_clng]

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

[jeanseb]

Could you help me please, @v-sihou-msft ?

Thanks a lot