how to calculate the number of people logged in for so many days over a period

[Cobra77]

Hi,

we would like a simple graph like the one presented below which simply says the number of people who have logged in so many times during the selected period, for example over the last 30 days from September 1st

in this example over the last 30 days

- 200 people connected 20 days

-150 people connected 14 days etc...

We have a big fact table where one person can log in 40 times on the same day, just once should be counted

I have already produced an aggregation table per day and per product which can be used subsequently, to reduce the volume.

GROUPBY( Microsoft,
Microsoft[userName]
, Microsoft[FK_IdCalendrier]
,Microsoft[productName]
, "Nb Cnx"
,COUNTX(CURRENTGROUP(),Microsoft[FK_IdCalendrier]))

How finish ? and generate the axis number connexions dynamically ?

Thanks for your help.

Best regards

[amitchandak]

@Cobra77 , Can you share sample data and sample output in table format?

[Cobra77]

@amitchandak 

ok i can a example with the result 

GROUPBY( Microsoft,
Microsoft[userName]
, Microsoft[FK_IdCalendrier]
,Microsoft[productName]
, "Nb Cnx"
,COUNTX(CURRENTGROUP(),Microsoft[FK_IdCalendrier]))

username ; FK_IdCalendrier ; ProductName ; Nb Cnx

toto@microsoft.Com  ;  20200815 ;  Product 1 ; 25

toto@microsoft.Com  ;  20200816 ;  Product 1 ; 2

toto@microsoft.Com  ;  20200822 ;  Product 1 ; 24

toto@microsoft.Com  ;  20200825 ;  Product 1 ; 36

titi@microsoft.Com  ;  20200813 ;  Product 1 ; 32

titi@microsoft.Com  ;  20200816 ;  Product 1 ; 22

titi@microsoft.Com  ;  20200821 ;  Product 1 ; 22

tUtU@microsoft.Com  ;  20200807 ;  Product 1 ; 45

tUtU@microsoft.Com  ;  20200818 ;  Product 1 ; 27

tUtU@microsoft.Com  ;  20200828 ;  Product 1 ; 26

tiUUti@microsoft.Com  ;  20200811 ;  Product 1 ; 4

tiUUti@microsoft.Com  ;  20200817 ;  Product 1 ; 4

tiUUti@microsoft.Com  ;  20200820 ;  Product 1 ; 6

with this little example, that would give , without date context :

3 peoples connected 3 times

1 people connected 4 times

A graph with 2 columns in this cases ( else see photo in first message )

does this help you ?

thanks

[richbenmintz · Accepted solution]

Hi @Cobra77 ,

What you can do is create a disconnected Dimension to be used on the X axis like the calcalated table below

buckets = GENERATESERIES(1, 50) 

 then create the following measure

User By Bucket = 
if(HASONEVALUE(buckets[bucket]), 
CALCULATE(COUNTX(GROUPBY( Microsoft,Microsoft[userName],Microsoft[productName], "Nb Cnx",COUNTX(CURRENTGROUP(),Microsoft[FK_IdCalendrier])), [Nb Cnx]), 
    filter(GROUPBY( Microsoft,Microsoft[userName],Microsoft[productName], "Nb Cnx",COUNTX(CURRENTGROUP(),Microsoft[FK_IdCalendrier])), [Nb Cnx]  = VALUES(buckets[bucket]))), BLANK())

which will create the following output

Hope this Helps,
Richard

Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

[Cobra77]

Hi @richbenmintz 

perfect its ok 

thanks