Joining Two Tables

[Babakhsn]

Hello Everyone,

I have the two following tables:

Task Table:

Project Table:

In the task table, I want to have the last column (Status). It is not originally there, it's part of project table. I want to do a join between the two tables based on Projectid, but I haven't been successful so far with DAX.

Could someone guide me?

Thanks in advance.

[v-zhangti · Accepted solution]

Hi, @Babakhsn 

You can try the following methods.

Column = CALCULATE(MAX('Project Table'[Status]),FILTER('Project Table',[Projectid]=EARLIER('Task Table'[Related Project Id])))

Column 2 = LOOKUPVALUE('Project Table'[Status],'Project Table'[Projectid],[Related Project Id])

Is this the result you expect? Please see the attached document.

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.

[FreemanZ]

hi @Babakhsn ,

try to add a calculated column like:

Status =
LOOKUPVALUE(
    Project[Status],
    Project[Project],
    Task[Related Project]
)

[philouduv]

Hello @Babakhsn ,
In the Table view go on the Task Table(select it on the right side panel), click on new column and write :

- If a relation exist between the two tables (one to many on the ProjectID ==> Related project ID):
Status = CALCULATE(SELECTEDVALUE(Project_table[Status]))
-else (Meaning we would need to introduce the relationship directly into the dax formula)

Status = CALCULATE(SELECTEDVALUE(Project_table[Status]),FILTER(Project_table, Project_table[ProjectID] = Task_Table[Related project ID]))

Best regards,