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
Rockwell
New Member

Finding subset that satisfies date condition

Hi,

I have a dataset with the following format:

UserID, Date, UniqueID

UserIDDateUnique ID
1Nov 20211
2Nov 20212
3Nov 20213
1Dec 20214
2Dec 20215
1Jan 20226
2Jan 20227
4Jan 20228

 

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

1 ACCEPTED SOLUTION
DataInsights
Super User
Super User

@Rockwell,

 

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:

 

DataInsights_0-1660224752264.png

 

3. Result:

 

DataInsights_1-1660224770383.png

 





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

Proud to be a Super User!




View solution in original post

2 REPLIES 2
v-henryk-mstf
Community Support
Community 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

DataInsights
Super User
Super User

@Rockwell,

 

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:

 

DataInsights_0-1660224752264.png

 

3. Result:

 

DataInsights_1-1660224770383.png

 





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

Proud to be a Super User!




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.