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
jagostinhoCT
Post Partisan
Post Partisan

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

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
Ashish_Mathur
Super User
Super User

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/

@Ashish_Mathur not clear on what you mean, sorry.

How to drag the user column to the row labels?

Zubair_Muhammad
Community Champion
Community Champion

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
        )
    )

Regards
Zubair

Please try my custom visuals

@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


Regards
Zubair

Please try my custom visuals

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)

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.

 

 

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?

Hi,

 

Is it my method that worked?


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

@Ashish_Mathur I ended up using one of the other formulas above and they worked. I did not use your method because I could not understand fully what you were suggesting.

 

But I am keen to try it too. Can you please clarify on how to drag the user column?

Simply drag and drop the way you do with a Pivot Table.


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

@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

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.