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
ShabnamKhan
Frequent Visitor

Isolate users missing an activity during a filtered time frame

Greetings! 
I have always found this forum to be super helpful and I am returning for more!

 

My first table is a list of subscribers with various activities aggregated on a monthly level. For each subscriber there is a record with emails, social posts, blog post, greeting cards post and events activity per month.

 

Data sample

subscriber dateemailssocialblogeventsgreeting cards sent
A1/1/202251506
A2/1/202245204
A3/1/202210859
A4/1/202256320
A5/1/202225035
A6/1/202200546
A7/1/202205520
A8/1/202201110
A9/1/202251506
A10/1/202245204
A11/1/202210859
A12/1/202251506
A1/1/202345204
A2/1/202310859
A3/1/202356320
B1/1/202225035
B2/1/202200546
B3/1/202205520
B4/1/202201110
B5/1/202251506
B6/1/202245204
B7/1/202210859
B8/1/202251506
B9/1/202245204
B10/1/202210859
B11/1/202256320
B12/1/202225035
B1/1/202300546
B2/1/202305520
B3/1/202301110
C1/1/202251506
C2/1/202245204
C3/1/202210859
C4/1/202251506
C5/1/202245204
C6/1/202210859
C7/1/202256320
C8/1/202225035
C9/1/202200546
C10/1/202205520
C11/1/202201110
C12/1/202251506
C1/1/202345204
C2/1/202310859

I have a date dimension table marked as the date table and connected to the first table via a date key. The requirement is to be able to identify users that perform (or didn't perform) an individual activity during the filtered time frame.

 

Approach taken, I created five indicator columns with values “yes” and “no” based on the individual activity like below.

Sent Emails? =   IF([AllEmailsCount]>0, "Yes", "No")

Then, I brought those columns as slicers in my report as shown below.

ShabnamKhan_1-1680036981640.png

 

It is not working properly, meaning it’s still listing users who had activity in the timeframe when filtered. That is because in that selected time frame the users have activities in some months and not other months. That’s why the user is still showing up as not having an activity whereas he/she has activity in the actual dataset.


I followed the same approach using a running total to see if this will help but it's not helping either. Can anyone please help?

@Ashish_Mathur you helped me last time, how about this time?

Thank you so much!

1 ACCEPTED SOLUTION

Hi,

Is this the result you are expecting?  You may download the PBI file from here.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

9 REPLIES 9
Ashish_Mathur
Super User
Super User

Hi,

Based on the data that you have shared, show the expected result very clearly..


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Thank you for looking into my problem. I do not have a specific format to return the result. I am open to ideas here. We want to find users who have missed a certain activities for a selected time frame. So, if user "A" has sent emails but did not send a blog post on October 2022, when selected that date range and deselect activity type "blog post" we should not see user A. But, currently I am being unable to do so (see image). Here, I have selected all but emails but I am still showing the customer because he has sent emails in that time frame. The expected result should not even return him. I hope this makes sense, please let me know otherwise. Thanks so much!

ShabnamKhan_0-1680109840675.png

 

Hi,

For the selected time period, do you want the table to show those rows which correspond to users:

  1. who did not engage in the selected activities; or
  2. who engaged in the selected activities

Please clarify.  Share the download link of the PBI file.

  1.  

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Thank you so much for replying. Yes, it's the first scenario where we want to return those rows which correspond to users who did not engage in the selected activities for a selected time frame. You will see how it's not currently working in my pbix file. Thank you again!

Here is the pbix file attached along with the sample data. Please let me know if you have any other question. Regards! 

Hi,

Is this the result you are expecting?  You may download the PBI file from here.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Yes, it worked! Thank you so much. I knew you would be able to solve it. God bless you.

You are welcome.  Thank you for yoru wishes.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
ShabnamKhan
Frequent Visitor

@SivaMani 
Thank you for your quick response. The issue still persists. After pivoting the table, I brought in the attribute values as slicer and when I select all the activities except emalis it lists customer 5452. However, this customer in fact had email sent activities in that date range as shown in the second image.

ShabnamKhan_0-1680043643704.png

ShabnamKhan_1-1680043750807.png

 

Hope we are able to find an appropriate solution. Let me know if you have any quesiton, thanks!

SivaMani
Resident Rockstar
Resident Rockstar

@ShabnamKhan, I would suggest you use UNPIVOT COLUMNS option in Power Query. It will help you to meet your requirement,

SivaMani_0-1680038545847.png

SivaMani_1-1680038604725.png

 

You can use the Attribute column in Slicer and create measures filtering this column.

 

I hope this helps!

 

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.