cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Dax calculations with Power BI

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

forum_powerbi_jsm_c.png

 

6 REPLIES 6
Highlighted
Microsoft
Microsoft

Re: Dax calculations with Power BI

@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".

 

 

88.PNG

 

Regards,

Highlighted
Frequent Visitor

Re: Dax calculations with Power BI

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.

 

forum_powerbi_jsm_d.png

 

Thank you very much if you can help me! 

Highlighted
Frequent Visitor

Re: Dax calculations with Power BI

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

Thanks a lot

Highlighted
Memorable Member
Memorable Member

Re: Dax calculations with Power BI

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
Highlighted
Frequent Visitor

Re: Dax calculations with Power BI

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!

 

forum_powerbi_jsm_e.png

Highlighted
Memorable Member
Memorable Member

Re: Dax calculations with Power BI

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

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors