Forum Discussion
Need Dax calculated column for the below condition
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.
Hi Anonymous
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
7 Replies
- Musadev
Resolver III
Hi Anonymous
as per the relationships, you want to show multiple customer_ids in the same cell/row, right? 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 formDealerID 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
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
Hi Anonymous
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
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
