Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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.
Solved! Go to 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
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
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
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.
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
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.
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.
Hi @SwathiJanu
as per the relationships, you want to show multiple customer_ids in the same cell/row, right?
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
25 | |
20 | |
18 | |
18 | |
15 |
User | Count |
---|---|
36 | |
19 | |
19 | |
17 | |
11 |