Hello There,
I need urgent help in writing DAX query with evaluate function for SSRS building report.
We have two tables, F_workorder and D_Approval with Many to Many relationship given.
We need to write a selectcolumns taking F_workorder as reference table and writing related() function on D_approval to get the results.
However we are unable to get the results when we directly give the related() function on D_approval though there is many to many relationship built.
We tried two ways as below
1st way:
EVALUATE
CALCULATETABLE(
GENERATEALL(
F_WORK_ORDER,
var table1=F_WORK_ORDER[WORK_ORDER_KY]
RETURN
SELECTCOLUMNS(
CALCULATETABLE(ApprovalID_LookUp_Transaction,ApprovalID_LookUp_Transaction[WORK_ORDER_KY]=table1),
"Approver",RELATED(D_APPROVAL[SUBMITTER_BY]),
"Approval_STATUS",RELATED(D_APPROVAL[STATUS_DS]),
"Approval_supportGroup",RELATED(D_APPROVAL[ASSIGNED_GROUP_NM]),
"Work_order_number_id",RELATED(D_WORK_ORDER[WORK_ORDER_NUMBER_ID]),
"Submit_date", F_WORK_ORDER[SUBMITTED_TS],
"Product_Name", RELATED ( D_work_order[PRODUCT_NM] )
)),
F_WORK_ORDER[DATA_SOURCE_ID]=****,
F_WORK_ORDER[SUBMITTED_TS] >= VALUE ( "1/1/2019" ),
F_WORK_ORDER[SUBMITTED_TS] < VALUE ( "8/30/2019" ),
D_TRANSACION_COMPANY[COMPANY_NM] = "----------")
Note: this is giving only the results for those approval fields are filled in, if the approval fields are not filled in for any work_order’s then those are not appearing in the query results at all.
2nd way:
EVALUATE
CALCULATETABLE (
SELECTCOLUMNS (
F_WORK_ORDER,
"Work_rder",RELATED(D_WORK_ORDER[WORK_ORDER_NUMBER_ID]),
"Submit_date", F_WORK_ORDER[SUBMITTED_TS],
"Product_Name", RELATED ( D_work_order[PRODUCT_NM] ),
"Approval_status", LOOKUPVALUE (
D_APPROVAL[STATUS_DS],
D_APPROVAL[APPROVAL_KY], min(ApprovalID_LookUp_Transaction[KEYCOLUMN])
),
"Approver", LOOKUPVALUE (
D_APPROVAL[SUBMITTER_BY],
D_APPROVAL[APPROVAL_KY], min ( ApprovalID_LookUp_Transaction[KEYCOLUMN] )
),
"Approval_supportgroup", LOOKUPVALUE (
D_APPROVAL[ASSIGNED_GROUP_NM],
D_APPROVAL[APPROVAL_KY], min ( ApprovalID_LookUp_Transaction[KEYCOLUMN] )
) ),
F_WORK_ORDER[DATA_SOURCE_ID] = ***,
F_WORK_ORDER[SUBMITTED_TS] >= VALUE ( "12/1/2019" ),
F_WORK_ORDER[SUBMITTED_TS] < VALUE ( "12/30/2019" ),
D_TRANSACION_COMPANY[COMPANY_NM] = "---------")
Note: We are getting same results in all the columns
We are unable to use naturalleftouterjoin or lookup which ideally should be very helpful.
Please suggest
