cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
csh8428
New Member

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

iddescriptionfinal_report_date
5Green12/30/2022
10Yellow12/29/2022
15Red 

 

public ops_audit_items

ops_audit_idtitleoriginal_due_date
5Announcement Email11/1/2022
5Report Issuance11/20/2022
10Announcement Email11/2/2022
10Report Issuance11/15/2022
15Announcement Email11/3/2022
15Report Issuance11/18/2022

 

public ops_audits Results

iddescriptionfinal_report_datereport_date_Calc
5Green12/30/202212/30/2022
10Yellow12/29/202212/29/2022
15Red 11/18/2022

 

 

Thanks for any help!

 

Craig

1 ACCEPTED SOLUTION
DataInsights
Super User
Super User

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

 

DataInsights_0-1674750824437.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

1 REPLY 1
DataInsights
Super User
Super User

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

 

DataInsights_0-1674750824437.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
Join Arun Ulag at MPPC23

Join Arun Ulag at MPPC23

Get a sneak peek into this year's Power Platform Conference Keynote.

PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Top Solution Authors