Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
kdewald
New Member

Dynamic groupby calculation

Hi everybody,

 

I am working on a dataset with the following columns, used to log visits to a website:

  • person_id
  • visit_id
  • visit_datetime

What I am looking for is to visuallize how many people did visit the website one, two, three times (etc) during a period of time defined by a slicer.

 

I was able to generate a calculated table with the following formula, but the output I get is from the entire dataset, not the selected period of time.

 

aux_frequency = SUMMARIZE(visits,visits[person_id],"visit_count",COUNT(visits[visit_id]))

Any help will be deeply appreciated. Thanks!

4 REPLIES 4
v-lili6-msft
Community Support
Community Support

hi, @kdewald

     You just need to add a measure as below:

visit_count = COUNT(visits[visit_id])

and then drag field person_id and this measure into table visual, then drag field visit_datetime into slicer to dynamic group by

 

Result:

6.PNG7.PNG

 

Best Regards,

Lin

 

 

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

@kdewald Please post some sample data to replicate and test/resolve your scenario....





Did I answer your question? Mark my post as a solution!

Proud to be a PBI Community Champion




Sure, here is an example:

 

person_id,visit_id,visit_datetime
A,0,2018-05-04
A,1,2018-05-19
A,2,2018-06-01
A,3,2018-06-18
A,4,2018-07-01
A,5,2018-07-20
A,6,2018-08-02
A,7,2018-08-09
A,8,2018-08-27
A,9,2018-09-14
B,0,2018-05-05
B,1,2018-05-19
B,2,2018-06-06
B,3,2018-06-24
B,4,2018-07-02
C,0,2018-07-21
C,1,2018-08-06
C,2,2018-08-23
C,3,2018-09-17
C,4,2018-10-05
D,0,2018-05-06
D,1,2018-05-23
D,2,2018-06-13
D,3,2018-07-09
D,4,2018-07-25
E,0,2018-07-22
E,1,2018-08-14
E,2,2018-09-10
E,3,2018-10-05
E,4,2018-11-01

@kdewald Thanks for posting sample data.

 

I've tried below to solve your scenario...

 

Created a table with single column from the visit_id values (distinct values) as below

 

VisitsFilter = DISTINCT(VisitCount[visit_id])

Create a relationship between main source table (In my case it is VisitCount) with the VisitsFilter that was created above with visit_id field.

 

use visit_id from VisitsFilter as slicer and use source table data for visualizing the data.

 

image.png

 

Hope this is what you are looking for...





Did I answer your question? Mark my post as a solution!

Proud to be a PBI Community Champion




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.