Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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
Solved! Go to Solution.
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.
Proud to be a 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.
Proud to be a Super User!
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
55 | |
54 | |
54 | |
37 | |
29 |
User | Count |
---|---|
77 | |
62 | |
45 | |
40 | |
40 |