Help with calculated field using 2 different tables

[csh8428]

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 tables.

Here’s the deal:

I have 2 tables

1: public ops_audits; which is 1 record per audit(ID)

2: public ops_audit_items; which has multiple rows per audit ID. This table has the milestones for each audit.

I defined the relationship in the query editor as 1 to many by joining on public ops_audits.id = public ops_audit_items.ops_audit_id

I’m not familiar with the DAX syntax, so here’s the natural language version of the formula I’m trying to create.

I want to add a “calculated field” to public ops_audits with the following formula:

Report_date_calc =

IF public ops_audits.final_report_date is not blank then ops_audits.final_report_date

ELSE public ops_audit_items.original_due_date when public ops_audit_items.title = “Report Issuance”

Below is a small sample with the logic defined in the results table at the bottom.

public ops_audits

id	description	final_report_date
5	Green	12/30/2022
10	Yellow	12/29/2022
15	Red

public ops_audit_items

ops_audit_id	title	original_due_date
5	Announcement Email	11/1/2022
5	Report Issuance	11/20/2022
10	Announcement Email	11/2/2022
10	Report Issuance	11/15/2022
15	Announcement Email	11/3/2022
15	Report Issuance	11/18/2022

public ops_audits Results

id	description	final_report_date	report_date_Calc
5	Green	12/30/2022	12/30/2022
10	Yellow	12/29/2022	12/29/2022
15	Red		11/18/2022

Thanks for any help!

Craig

[DataInsights · Accepted solution]

@csh8428,

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.