Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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]
)
OrderDetails
Contracts
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.
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?
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.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
118 | |
101 | |
71 | |
61 |