March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Register NowGet certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare 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
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Check out the November 2024 Power BI update to learn about new features.
User | Count |
---|---|
121 | |
98 | |
85 | |
69 | |
61 |
User | Count |
---|---|
138 | |
120 | |
109 | |
99 | |
97 |