Create measure or column that specifies conditions from two related tables

[Chris2016]

Hi,

I need some help with the following issue:

I have two tables which are related by Name (many to one, single cross filter direction):

Users:

Name	Country
John	USA
Mark	Canada
Lily	France
Jenny	Canada
June	USA
Marie	France

Activities:

Name	ID
John	123
John	456
June	123
Jenny	123
Jenny	456
Marie	123

I need to create a measure or a column in the Users table that specifies the following ineligibility condition: The Name must be from Country "Canada", and the ID must be "123". in the above example, the result (or flag) would be Jenny. Creating the Country ineligibility output is done, but the ID output from the users table is not recognized. Any ideas on how that can be done?

Many thanks!

[wdx223_Daniel]

the users table must be unique,then

CalculatedColumn=IF(Activities[ID]="123"&&RELATED(Users[Country])="Canada",Activities[Name])

and for DAX, Users[Name] is in the rows,

Measure=IF(MAX(Users[Country])="Canada",MAXX(FILTER(Activities,Activities[ID]="123"),Activities[Name]))

[v-zhangti · Accepted solution]

Hi, @Chris2016 

You can try the following methods.
Measure:

Measure = 
Var _N1=CALCULATE(MAX(Users[Country]),FILTER(ALL(Users),[Name]=SELECTEDVALUE(Activities[Name])))
Return
IF(_N1="Canada"&&SELECTEDVALUE(Activities[ID])=123,SELECTEDVALUE(Activities[Name]),"Null")

Column:

Column = 
Var _N1=CALCULATE(MAX(Users[Country]),FILTER(ALL(Users),[Name]=EARLIER(Activities[Name])))
Return
IF(_N1="Canada"&&[ID]=123,[Name],BLANK())

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Chris2016]

Hi, John,

I cannot add a column in the Activities table, because that is not where I need it, as only a few of the users are present in that table and the new column must account for every user in the Users table.

I already tried using RELATED with a new column in the Users table, but the "ID" column from Activities is not recognised (the error is that it either does not exist, or there is no relationship between the tables - although that is not true). 
Thanks!

[johnt75]

RELATED only works from the many side to the one side of a relationship, to go the other way you need RELATEDTABLE.

Try this as a column on Users.

Eligible = 'Users'[Country] = "Canada" && NOT ISEMPTY( FILTER( RELATEDTABLE( 'Activities'), 'Activities'[ID] = 123))

[johnt75]

You could create a column on the Activities table like

Eligibile = 'Activities'[ID] = 123 && RELATED('Users'[Country]) = "Canada"