Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
SwathiJanu
Frequent Visitor

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. 

1 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. 

Musadev_0-1713194597648.png

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.

Musadev_1-1713194703162.png

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)

Musadev_2-1713194786085.png

Step 5:

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

Musadev_3-1713194879945.png


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

 

 

 

 

View solution in original post

7 REPLIES 7
Pawasthi3292
Frequent Visitor

Need some clarification:-

Pawasthi3292_0-1713200128208.png

Dealer

Pawasthi3292_1-1713200150914.png

 

Inspections

Pawasthi3292_2-1713200174145.png

 

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 

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
Frequent Visitor

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. 

Musadev_0-1713194597648.png

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.

Musadev_1-1713194703162.png

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)

Musadev_2-1713194786085.png

Step 5:

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

Musadev_3-1713194879945.png


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
Frequent Visitor

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

IDDealer Code
101Project
102Project
103A101


Inspections table data is in below form

DealerIDCustomer ID
101b102
102b203
103b104

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:

Musadev_0-1713184804206.png

Inspections Table:

Musadev_1-1713184836913.png

 

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

Musadev_2-1713184905390.png

 

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
Resolver III
Resolver III

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

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.