cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

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

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.

1 ACCEPTED SOLUTION
Community Support

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 REPLIES 3
Super User

@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
Not applicable

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

Community Support

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors