Forum Discussion
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 | date | emails | social | blog | events | greeting cards sent |
| A | 1/1/2022 | 5 | 1 | 5 | 0 | 6 |
| A | 2/1/2022 | 4 | 5 | 2 | 0 | 4 |
| A | 3/1/2022 | 1 | 0 | 8 | 5 | 9 |
| A | 4/1/2022 | 5 | 6 | 3 | 2 | 0 |
| A | 5/1/2022 | 2 | 5 | 0 | 3 | 5 |
| A | 6/1/2022 | 0 | 0 | 5 | 4 | 6 |
| A | 7/1/2022 | 0 | 5 | 5 | 2 | 0 |
| A | 8/1/2022 | 0 | 1 | 1 | 1 | 0 |
| A | 9/1/2022 | 5 | 1 | 5 | 0 | 6 |
| A | 10/1/2022 | 4 | 5 | 2 | 0 | 4 |
| A | 11/1/2022 | 1 | 0 | 8 | 5 | 9 |
| A | 12/1/2022 | 5 | 1 | 5 | 0 | 6 |
| A | 1/1/2023 | 4 | 5 | 2 | 0 | 4 |
| A | 2/1/2023 | 1 | 0 | 8 | 5 | 9 |
| A | 3/1/2023 | 5 | 6 | 3 | 2 | 0 |
| B | 1/1/2022 | 2 | 5 | 0 | 3 | 5 |
| B | 2/1/2022 | 0 | 0 | 5 | 4 | 6 |
| B | 3/1/2022 | 0 | 5 | 5 | 2 | 0 |
| B | 4/1/2022 | 0 | 1 | 1 | 1 | 0 |
| B | 5/1/2022 | 5 | 1 | 5 | 0 | 6 |
| B | 6/1/2022 | 4 | 5 | 2 | 0 | 4 |
| B | 7/1/2022 | 1 | 0 | 8 | 5 | 9 |
| B | 8/1/2022 | 5 | 1 | 5 | 0 | 6 |
| B | 9/1/2022 | 4 | 5 | 2 | 0 | 4 |
| B | 10/1/2022 | 1 | 0 | 8 | 5 | 9 |
| B | 11/1/2022 | 5 | 6 | 3 | 2 | 0 |
| B | 12/1/2022 | 2 | 5 | 0 | 3 | 5 |
| B | 1/1/2023 | 0 | 0 | 5 | 4 | 6 |
| B | 2/1/2023 | 0 | 5 | 5 | 2 | 0 |
| B | 3/1/2023 | 0 | 1 | 1 | 1 | 0 |
| C | 1/1/2022 | 5 | 1 | 5 | 0 | 6 |
| C | 2/1/2022 | 4 | 5 | 2 | 0 | 4 |
| C | 3/1/2022 | 1 | 0 | 8 | 5 | 9 |
| C | 4/1/2022 | 5 | 1 | 5 | 0 | 6 |
| C | 5/1/2022 | 4 | 5 | 2 | 0 | 4 |
| C | 6/1/2022 | 1 | 0 | 8 | 5 | 9 |
| C | 7/1/2022 | 5 | 6 | 3 | 2 | 0 |
| C | 8/1/2022 | 2 | 5 | 0 | 3 | 5 |
| C | 9/1/2022 | 0 | 0 | 5 | 4 | 6 |
| C | 10/1/2022 | 0 | 5 | 5 | 2 | 0 |
| C | 11/1/2022 | 0 | 1 | 1 | 1 | 0 |
| C | 12/1/2022 | 5 | 1 | 5 | 0 | 6 |
| C | 1/1/2023 | 4 | 5 | 2 | 0 | 4 |
| C | 2/1/2023 | 1 | 0 | 8 | 5 | 9 |
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.
Then, I brought those columns as slicers in my report as shown below.
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!
9 Replies
ShabnamKhan, I would suggest you use UNPIVOT COLUMNS option in Power Query. It will help you to meet your requirement,
You can use the Attribute column in Slicer and create measures filtering this column.
I hope this helps!
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.Hope we are able to find an appropriate solution. Let me know if you have any quesiton, thanks!
Hi,
Based on the data that you have shared, show the expected result very clearly..
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!
Hi,
For the selected time period, do you want the table to show those rows which correspond to users:
- who did not engage in the selected activities; or
- who engaged in the selected activities
Please clarify. Share the download link of the PBI file.
