Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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
vResult
2. Add measure User Filter to a visual:
3. Result:
Proud to be a Super User!
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
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
vResult
2. Add measure User Filter to a visual:
3. Result:
Proud to be a Super User!
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |