DAX: Count visit frequency per client id and period (DimDate Slicer)

[Anonymous]

Hi all,

After 1.5 Days of trial and error and no solution I post it here...

I have a table as "log" file with client id, log time and occasion (only occasion = "sitevisit", rest irrelevant) similar like this sample data:

client id	time	occasion
1	2021.01.01	sitevisit
2	2021.01.01	hq_input
3	2021.01.02	sitevisit
1	2021.01.01	sitevisit
4	2021.01.02	supervision
2	2021.01.03	sitevisit
3	2021.01.05	sitevisit

I want to calculate two things, both need to be dynamic through a time slicer (DimDate) and are for a matrix or table visual.

1.) How many times has a client id logged in during a certain time period? (DimDate Slicer)

Solution:

client id	nVisits
1	2
2	2
3	2
4	1

2.) How many clients log in 1x, 2x, 3x, n, ... in a certain time period? (same slicer as nr. 1 and nVisits should be dynamic if there would be more than 2, 3, n visits )

Solution:

nVisits	(how many) clients
1	3
2	1

Would be great if you could also share your thoughts on the formulas, because I m kinda new to Dax and want to understand the whole prozess.

Thanks in advance

[amitchandak]

@Anonymous , You need to create bucketing/binning on visit measure

Create a new table

bucket = generateseries(1,20,1)

new measure = countx(filter(values(Table[Client]), [nVisit] = max(buckte[Value]), [Client])

refer this

Dynamic Segmentation Bucketing Binning
https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-Segmentation-Bucketing-Binning/m-p/1387187#M626

Dynamic Segmentation, Bucketing or Binning: https://youtu.be/CuczXPj0N-k

[Anonymous]

@amitchandak , thx for the input! But how do I calculate the nVisits per client id (Nr.1)? I thought something like this:

nVisits = COUNTX("log", FILTER("log", "log"[occasion] = "sitevisit" && "log"[client_id] && "DimDate"))

[Anonymous · Accepted solution]

Hi @Anonymous ,

I created a sample pbix file(see attachment) for you base on the provided data, please check whether that is what you want.

1. Create a measure to get the number of times each user visited the site base on the date slicer selections

nVisits = 
VAR _tab =
    SUMMARIZE (
        FILTER ( 'log', 'log'[occasion] = "sitevisit" ),
        'log'[client_id],
        "numberofvisitsite", CALCULATE ( COUNT ( 'log'[client_id] ) )
    )
RETURN
    SUMX ( _tab, [numberofvisitsite] )

2. Create a visit times dimension table and a measure as below to get the count of clients for different visit site times just as suggestd by @amitchandak 

Visit times = GENERATESERIES ( 1, 100, 1 )

Measure = 
COUNTX (
    FILTER (
        VALUES ( 'log'[client_id] ),
        SELECTEDVALUE ( 'Visit times'[Visit times] ) = [nVisits]
    ),
    [client_id]
)

Count of client base on visit times = SUMX ( VALUES ( 'Visit times'[Visit times] ), [Measure] )

Best Regards