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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
sydneylw
Regular Visitor

Creating a new table from columns of other tables

I need a column that pulls the order_id from OrderDetails when the study_id from OrderDetails = 79 This will be repeated with a second column based on the results of this column where the contract_id from Contracts when the order_id equals the value in the new column.

 

1 Data Details =
IF(
'OrderDetails'[study_id] = 79,
'OrderDetails'[order_id]
)

5 REPLIES 5
v-luwang-msft
Community Support
Community Support

Hi @sydneylw ,

Could you pls share a sample data and expect output?

 

 

Best Regards

Lucien

OrderDetails

sydneylw_0-1650568279994.png  

Contracts

sydneylw_1-1650568346895.png

So the issue I am running into is I need a new table with a column for  each of the following order_id, Contract ID, Contract Status, and Is Current? for each study_id. I have tried the following though probably isn't the easiest way to just add a column for order_id for each study_id to the OrderDetails table. I then transformed the contracts table to keep only Yes for Is Current? and Approved, In Process, and Expired for Contract Status. I am copying the OrderDetails table for each study_id and then sorting by one study_id and creating a visual for each study_id by Contract Status.

sydneylw
Regular Visitor

The Dax code that I posted originally works when working in "OrderDetails" dataset or table. This however doesn't work when trying to create a new table and pull in this data from "Order Details" Any suggestions?

amitchandak
Super User
Super User

@sydneylw ,

New column in Contracts master

new column =
var _cnt = countx(filter('OrderDetails', 'OrderDetails'[study_id] =79 && 'Contracts'[order_id] ='OrderDetails'[order_id]) ,'OrderDetails'[order_id])
return
_cnt

 

or

new column =
var _cnt = countx(filter('OrderDetails', 'OrderDetails'[study_id] =79 && 'Contracts'[order_id] ='OrderDetails'[order_id]) ,'OrderDetails'[order_id])
return
if(isblank(_cnt), blank(), 'OrderDetails'[order_id] )

 

refer 4 ways to copy data from one table to another
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8

Thank you for this but it doesn't work for some reason and I am pretty sure this isn't exactly what I need. I can't get a count here because there is a further step of current_order. I really just need a column that reads in order_id from OrderDetails when the study_id from OrderDetails = 79. I will repeat this step for two more columns I don't want to do all of it in one column because the count will not be accurate. Please help. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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