topic Joining Two Tables in DAX Commands and Tips

Joining Two Tables

Babakhsn — Mon, 27 Nov 2023 12:24:08 GMT

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.

Re: Joining Two Tables

philouduv — Mon, 27 Nov 2023 12:46:18 GMT

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,

Re: Joining Two Tables

FreemanZ — Mon, 27 Nov 2023 12:51:28 GMT

hi Babakhsn ,

try to add a calculated column like:

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

Re: Joining Two Tables

v-zhangti — Wed, 29 Nov 2023 02:09:43 GMT

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.