Forum Discussion
Solved
Help with calculated field using 2 different tables
I’m new to PBI, but have 10+years experience with Alteryx and Tableau. I’m having trouble figuring how to make calculated measures or columns(not sure which I need) that use criteria from 2 different...
You can achieve this with a calculated column or measure.
Calculated column:
Report_date_calc = VAR vFinalReportDate = 'public ops_audits'[final_report_date] VAR vOriginalDueDate = CALCULATE ( MAX ( 'public ops_audit_items'[original_due_date] ), 'public ops_audit_items'[title] = "Report Issuance" ) VAR vResult = IF ( ISBLANK ( vFinalReportDate ), vOriginalDueDate, vFinalReportDate ) RETURN vResultMeasure:
Report_date_calc_measure = VAR vFinalReportDate = MAX ( 'public ops_audits'[final_report_date] ) VAR vOriginalDueDate = CALCULATE ( MAX ( 'public ops_audit_items'[original_due_date] ), 'public ops_audit_items'[title] = "Report Issuance" ) VAR vResult = IF ( ISBLANK ( vFinalReportDate ), vOriginalDueDate, vFinalReportDate ) RETURN vResultThe DAX is identical except for line 2. Since a row context exists in a calculated column, an aggregate function like MAX isn't needed. However, in a measure, an aggregate function is needed.
DataInsights
Super User
Super User
You can achieve this with a calculated column or measure.
Calculated column:
Report_date_calc =
VAR vFinalReportDate = 'public ops_audits'[final_report_date]
VAR vOriginalDueDate =
CALCULATE (
MAX ( 'public ops_audit_items'[original_due_date] ),
'public ops_audit_items'[title] = "Report Issuance"
)
VAR vResult =
IF ( ISBLANK ( vFinalReportDate ), vOriginalDueDate, vFinalReportDate )
RETURN
vResult
Measure:
Report_date_calc_measure =
VAR vFinalReportDate = MAX ( 'public ops_audits'[final_report_date] )
VAR vOriginalDueDate =
CALCULATE (
MAX ( 'public ops_audit_items'[original_due_date] ),
'public ops_audit_items'[title] = "Report Issuance"
)
VAR vResult =
IF ( ISBLANK ( vFinalReportDate ), vOriginalDueDate, vFinalReportDate )
RETURN
vResult
The DAX is identical except for line 2. Since a row context exists in a calculated column, an aggregate function like MAX isn't needed. However, in a measure, an aggregate function is needed.