Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
No, I am not happy with this service.
COmmunity member asks me to go and search the blogs again.
Post searching in communities and blogs only I have raised my concern.
I am not happy with the suggestion.
I figured out myself and fixed it. thanks
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
41 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
42 | |
33 | |
30 | |
18 | |
18 |