cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted

Filter user that filled in both surveys

Hello,

 

I have a datasource with dates for which users filled in the same survey and I want to filter out the results for users that have filled the survey on both dates. Example below. The only users that should be on the new table are User1 and User3 since they are the only ones that sent both surveys in March 2017 and March 2018.

 

How can I get this in PBI Desktop?

 

Thanks

J

 

DateUser
Mar-17User1
Mar-18User1
Mar-17User2
Mar-17User3
Mar-18User3
Mar-18User 4
1 ACCEPTED SOLUTION

Accepted Solutions
v-xjiin-msft Super Contributor
Super Contributor

Re: Filter user that filled in both surveys

Hi @jagostinhoCT,

 

Since we can't see your real data. It is hard for us to guess where the issue is. So could you please share us your pbix file with One Drive or Google Drive if possible?

 

By the way, try to run the formula again by removing the .[Date]. See if it works for you.

 

Table Users =
VAR Users =
COUNTROWS(VALUES('PDF Printing'[Completion time]))
RETURN
FILTER(
ALL('PDF Printing'[Name]),
CALCULATE(DISTINCTCOUNT('PDF Printing'[Completion time]))=Users)

Thanks,
Xi Jin.

 

 

View solution in original post

12 REPLIES 12
Super User
Super User

Re: Filter user that filled in both surveys

Hi @jagostinhoCT

 

Try this calculated table

 

From Modelling TAb>>NEw Table

 

Table =
VAR totaldays =
    COUNTROWS ( VALUES ( Table1[Date] ) )
RETURN
    CALCULATETABLE (
        Table1,
        FILTER (
            ALL ( Table1[User] ),
            CALCULATE ( DISTINCTCOUNT ( Table1[Date] ) ) = totaldays
        )
    )
Super User
Super User

Re: Filter user that filled in both surveys

Super User
Super User

Re: Filter user that filled in both surveys

@jagostinhoCT

 

If you just need the name of users., you can use this calculated table

 

Table 2 =
VAR totaldays =
    COUNTROWS ( VALUES ( Table1[Date] ) )
RETURN
    FILTER (
        ALL ( Table1[User] ),
        CALCULATE ( DISTINCTCOUNT ( Table1[Date] ) ) = totaldays
    )

filter2.png

Re: Filter user that filled in both surveys

It works for the sample data but on my real data it is returning all users from both dates.

 

Here is the renaming I did to match the real data.

 

Table Users =
VAR Users =
COUNTROWS(VALUES('PDF Printing'[Completion time].[Date]))
RETURN
FILTER(
ALL('PDF Printing'[Name]),
CALCULATE(DISTINCTCOUNT('PDF Printing'[Completion time].[Date]))=Users)

v-xjiin-msft Super Contributor
Super Contributor

Re: Filter user that filled in both surveys

Hi @jagostinhoCT,

 

Since we can't see your real data. It is hard for us to guess where the issue is. So could you please share us your pbix file with One Drive or Google Drive if possible?

 

By the way, try to run the formula again by removing the .[Date]. See if it works for you.

 

Table Users =
VAR Users =
COUNTROWS(VALUES('PDF Printing'[Completion time]))
RETURN
FILTER(
ALL('PDF Printing'[Name]),
CALCULATE(DISTINCTCOUNT('PDF Printing'[Completion time]))=Users)

Thanks,
Xi Jin.

 

 

View solution in original post

Super User
Super User

Re: Filter user that filled in both surveys

Hi,

 

Drag the User column to the row labels.  Enter this measure

 

=if(DISTINCTCOUNT(Data[Date])>1,1,BLANK())

 

Hope this helps.


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

Re: Filter user that filled in both surveys

@v-xjiin-msft, fair point.

 

I have abbreviated the user names so that they cannot be identified.

The table below is based on the real data. User names abreviated and wanting to identify if user is showing in both Quarters 1 and 4.

For instance, the first user in which this happens is AHb.

 

Will this make it clearer?

 

Thank you

J

 

UserQuarter

ABB4
ACa1
AH 1
AHb4
AHb1
AK 1
ALw1
ASo4
BGd4
BVl1
CPn4
CWe4
DCC1
DHa1
DUU1
EMM4
HC 1
HPe4
IDL4
IDL1
JAo4
JBl4
JCa4
JFF4
JFF1
JHd4
JHh1
JJa4
JJa1
JRo4
JRo1
JSa1
JTo1
KPP1
KSm1
KVp1
LBa4
LCC4
LFt4
LFt1
LWe4
MAA4
MCp1
MCu4
MDD4
MDD1
MKi1
mmv4
MOa4
MPn1
MVt1
NDD4
NDD1
NGr1
PBr4
PBr1
PHu1
PKe4
PKe1
PKo1
PRR1
RBe1
SGo4
SGo1
SGu4
SMa4
SMa1
SPr4
TCr1
TKu4
TKu1
TMa4
TMa1
VPa4
VPa1
WMl4
YPv1

Re: Filter user that filled in both surveys

@Ashish_Mathur not clear on what you mean, sorry.

How to drag the user column to the row labels?

Re: Filter user that filled in both surveys

It is working now. 

 

Many thanks.

 

Just one more question. What to include in the formulas if I want the list of names that are NOT in both, or just on one of the QuarterNumbers?

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 497 members 4,434 guests
Please welcome our newest community members: