DAX - count unique values in one column, the filter it by a different table and column

[sharpedogs]

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 🙂  

[JarroVGIT · Accepted 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:

Table UsersTable Logins

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! 🙂

[JarroVGIT · Accepted 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:

Table UsersTable Logins

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! 🙂

[sharpedogs · Accepted solution]

ohhhh..... mercy.... Yes...

I would give you two thumbs up if i could