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.
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!
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 |
---|---|
108 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |