Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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
Solved! Go to 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
@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
@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"))
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
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
93 | |
85 | |
83 | |
74 | |
49 |
User | Count |
---|---|
142 | |
141 | |
110 | |
69 | |
55 |