Forum Discussion
Finding subset that satisfies date condition
Hi,
I have a dataset with the following format:
UserID, Date, UniqueID
| UserID | Date | Unique ID |
| 1 | Nov 2021 | 1 |
| 2 | Nov 2021 | 2 |
| 3 | Nov 2021 | 3 |
| 1 | Dec 2021 | 4 |
| 2 | Dec 2021 | 5 |
| 1 | Jan 2022 | 6 |
| 2 | Jan 2022 | 7 |
| 4 | Jan 2022 | 8 |
If I wanted to find the subset of UserIDs that are present at every date, but not those that aren't present at every date how would I approach it? I'm a but stuck here. For example, I'd want the subset of UserIDs that Showed up in November, December and January but not the ones that did not show up at all three dates.
Any help is appreciated
Thanks
Try this solution.
1. Create measure:
User Filter = VAR vCountUserRows = CALCULATE ( COUNTROWS ( Table1 ), ALL ( Table1 ), VALUES ( Table1[UserID] ) ) VAR vCountDistinctDate = CALCULATE ( COUNTROWS ( VALUES ( Table1[Date] ) ), ALL ( Table1 ) ) VAR vResult = IF ( vCountUserRows = vCountDistinctDate, 1 ) RETURN vResult2. Add measure User Filter to a visual:
3. Result:
2 Replies
- DataInsights
Super User
Try this solution.
1. Create measure:
User Filter = VAR vCountUserRows = CALCULATE ( COUNTROWS ( Table1 ), ALL ( Table1 ), VALUES ( Table1[UserID] ) ) VAR vCountDistinctDate = CALCULATE ( COUNTROWS ( VALUES ( Table1[Date] ) ), ALL ( Table1 ) ) VAR vResult = IF ( vCountUserRows = vCountDistinctDate, 1 ) RETURN vResult2. Add measure User Filter to a visual:
3. Result:
v-henryk-mstfCommunity Support
Hi Rockwell ,
Whether the advice given by DataInsights has solved your confusion, if the problem has been solved you can mark the reply for the standard answer to help the other members find it more quickly. If not, please point it out.
Looking forward to your feedback.
Best Regards,
Henry