Need Dax calculated column for the below condition

[SwathiJanu]

there are 2 tables dealer table and inspections table with one to many relationship (ID from dealer table and DealerID from inspections table are common columns). if the values dealercode of dealer table are as "Project" then return customerid from table2 else return dealercode table 1. I need dax calculated column for this condition. 

[Pawasthi3292]

Need some clarification:-

Dealer

Inspections

Is the data model correct as per your question, if yes what is the output your are looking for?
I updated the insepections table to give more clarity on 1 to M 

[Musadev]

Hi @Pawasthi3292 

Check the recent replies of the author,  the author is looking for a solution in the power query editor,  calculated column will not help. As per the post, 1-M is fine. I think,  the author is looking for the output mentioned in the last step

[SwathiJanu]

Thanks @Musadev , I appreciate your efforts.
but this gives me a new column with many values concatenated in a single row where dealercode is Project. again I have to perform split function and convert them to rows which is only possible in power query and this calculated column doesn't appear in power query. Looking for a more feasible solution.

[Musadev · Accepted solution]

Hi @SwathiJanu 

Alright, you can do the same actions in the Power Query Editor as well. Check the following Steps and replicate them for your Data. 

Step 1:
Create duplicate tables of Dealer and Inspection.

Step 2:

Merge both tables and keep the only customerID in the Dealer table. 

Step 3:

Group by the ID and DealerCode and use aggregation of sum on the CustomerID. You will get an error. Now change the Sum function of M Code to Text.Combine as mentioned below.

Step 4:

Apply the check to keep only for Project DealerCode. ( I am doing this for new columns, you can do the same transformation on the same column as well)

Step 5:

Split the latest column by delimiter and you will get multiple columns based on the data.

Now you can further transform this data as per the requirements. Save and Load the editor.

If the above steps resolve your Post then Mark it as a Solution to help the beginners. Thanks

[SwathiJanu]

lets say for example, Dealer table is having data in below format

ID	Dealer Code
101	Project
102	Project
103	A101

Inspections table data is in below form

DealerID	Customer ID
101	b102
102	b203
103	b104

I need customer id from Inspections table as a new column in dealer table where dealer code is Project else return dealer code.

[Musadev]

As you have mentioned in the Post, the relationship will be 1-Many then you can use the below steps to create a calculated Column based on your requirement. 
Dealer Table:

Inspections Table:

Based on the below DAX for the calculated column, check the DAX snippet and Screenshot of the result.

CONCATENATEX() has been used as the relationship is 1-M, however, if you have 1-1 relationship same CC will work for it. 

If the above steps resolve your Post then Mark it as a Solution to help the new comers. Thanks

[Musadev]

Hi @SwathiJanu 
as per the relationships, you want to show multiple customer_ids in the same cell/row, right?