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!
User | Count |
---|---|
103 | |
83 | |
68 | |
47 | |
47 |
User | Count |
---|---|
155 | |
91 | |
82 | |
69 | |
67 |