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.
I should be able to figure this out, but I'm stumped..
Table 1
- has login info with the principal piece of data being "User Name". There are alot of "User Name" duplicates beucase each day the user logs in it generates a new row.
Table 2
- Is a file with "User Name", all unique users.
** There are users in the Table 1 that are not in Table 2 and there are Users in Table 2 that are not in Table 1.
I want to write a DAX that counts all the unique "User Name" from the first table and then filters it to match the users in the Second. The output is a unique Set of Users that are in both tables..
I've tried multiple, calcualte, countdistinct and filter formulas but nothing seems to work... please help 🙂
Solved! Go to Solution.
Well this is an interesting question. You say you want a set of users to be returned so I assumed you are aware you want a calculated table. Please note that typically it helps us a lot if you create some copyable dummy data for us to experiment with 🙂
I created the following tables:
These tables have a 1-many relationship on UserID. I then created the following table;
UniqueJoins =
VAR _loginsID = SELECTCOLUMNS(Logins, "LoginID", Logins[UserID])
VAR _usersID = SELECTCOLUMNS(Users, "UserID", Users[UserID])
VAR _union = ADDCOLUMNS(UNION(_loginsID, _usersID), "IsInBothTables",
VAR _curID = [LoginID]
RETURN
IF(COUNTROWS(FILTER(Users, Users[UserID] = _curID)) > 0 &&
COUNTROWS(FILTER(Logins, Logins[UserID] = _curID)) >0, TRUE, FALSE))
RETURN
DISTINCT(FILTER(_union, [IsInBothTables] = TRUE()))
This returns the following set:
As you can see, both userID C and D are left out as well as YY and Z as they only occur in their own tables but not the other one.
Does this help? You can see my pbix here (ignore other tables as they are for other questions).
Kind regards
Djerro123
-------------------------------
If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.
Keep those thumbs up coming! 🙂
Proud to be a Super User!
ohhhh..... mercy.... Yes...
I would give you two thumbs up if i could
Well this is an interesting question. You say you want a set of users to be returned so I assumed you are aware you want a calculated table. Please note that typically it helps us a lot if you create some copyable dummy data for us to experiment with 🙂
I created the following tables:
These tables have a 1-many relationship on UserID. I then created the following table;
UniqueJoins =
VAR _loginsID = SELECTCOLUMNS(Logins, "LoginID", Logins[UserID])
VAR _usersID = SELECTCOLUMNS(Users, "UserID", Users[UserID])
VAR _union = ADDCOLUMNS(UNION(_loginsID, _usersID), "IsInBothTables",
VAR _curID = [LoginID]
RETURN
IF(COUNTROWS(FILTER(Users, Users[UserID] = _curID)) > 0 &&
COUNTROWS(FILTER(Logins, Logins[UserID] = _curID)) >0, TRUE, FALSE))
RETURN
DISTINCT(FILTER(_union, [IsInBothTables] = TRUE()))
This returns the following set:
As you can see, both userID C and D are left out as well as YY and Z as they only occur in their own tables but not the other one.
Does this help? You can see my pbix here (ignore other tables as they are for other questions).
Kind regards
Djerro123
-------------------------------
If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.
Keep those thumbs up coming! 🙂
Proud to be a Super User!
ohhhh..... mercy.... Yes...
I would give you two thumbs up if i could
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 |
---|---|
46 | |
28 | |
22 | |
12 | |
8 |
User | Count |
---|---|
76 | |
51 | |
44 | |
16 | |
12 |