Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Date | User |
Mar-17 | User1 |
Mar-18 | User1 |
Mar-17 | User2 |
Mar-17 | User3 |
Mar-18 | User3 |
Mar-18 | User 4 |
Solved! Go to 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.
Hi,
Drag the User column to the row labels. Enter this measure
=if(DISTINCTCOUNT(Data[Date])>1,1,BLANK())
Hope this helps.
@Ashish_Mathur not clear on what you mean, sorry.
How to drag the user column to the row labels?
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 ) )
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 )
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?
@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.
@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
ABB | 4 |
ACa | 1 |
AH | 1 |
AHb | 4 |
AHb | 1 |
AK | 1 |
ALw | 1 |
ASo | 4 |
BGd | 4 |
BVl | 1 |
CPn | 4 |
CWe | 4 |
DCC | 1 |
DHa | 1 |
DUU | 1 |
EMM | 4 |
HC | 1 |
HPe | 4 |
IDL | 4 |
IDL | 1 |
JAo | 4 |
JBl | 4 |
JCa | 4 |
JFF | 4 |
JFF | 1 |
JHd | 4 |
JHh | 1 |
JJa | 4 |
JJa | 1 |
JRo | 4 |
JRo | 1 |
JSa | 1 |
JTo | 1 |
KPP | 1 |
KSm | 1 |
KVp | 1 |
LBa | 4 |
LCC | 4 |
LFt | 4 |
LFt | 1 |
LWe | 4 |
MAA | 4 |
MCp | 1 |
MCu | 4 |
MDD | 4 |
MDD | 1 |
MKi | 1 |
mmv | 4 |
MOa | 4 |
MPn | 1 |
MVt | 1 |
NDD | 4 |
NDD | 1 |
NGr | 1 |
PBr | 4 |
PBr | 1 |
PHu | 1 |
PKe | 4 |
PKe | 1 |
PKo | 1 |
PRR | 1 |
RBe | 1 |
SGo | 4 |
SGo | 1 |
SGu | 4 |
SMa | 4 |
SMa | 1 |
SPr | 4 |
TCr | 1 |
TKu | 4 |
TKu | 1 |
TMa | 4 |
TMa | 1 |
VPa | 4 |
VPa | 1 |
WMl | 4 |
YPv | 1 |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |