Forum Discussion

Andreamary's avatar
Andreamary
New Member
8 years ago

DAX nested IF statements with two tables and subtraction between two columns

I have been trying to create a calculated column involving two tables and based on the criteria outlined below.

 

Table 1: 'UDS'

Includes the following two columns:

Column name: [Unit]

Contains one of the following 3 variables:

DC

PA

IFP

 

Column name: [Date_WO_Completed]

Contains date that work order was completed by the unit.

 

Table 2: 'AIM_Schedule'

[DC_Cut_Off] 'the deadline for DC to deliver their work orders to the next unit

[IFP_Cut_Off] 'the deadline for IFP to deliver their work orders to the next unit

[PA_Cut_Off] 'the deadline for PA to deliver their work orders to the next unit

 

I want to create a calculated column in the UDS table called [Timeliness by Unit], whereby if two conditions are met, then the work order is 'On Time', otherwise it is 'Late'.

 

My pseudo code is written below:

 

Timeliness by Unit =
IF 'UDS'[Unit]="DC", AND ('UDS'[Date_WO_Completed] <= RELATED(AIM_Schedule[DC_Cut_Off], THEN "On Time"
IF 'UDS'[Unit]="IFP", AND ('UDS'[Date_WO_Completed] <= RELATED(AIM_Schedule[IFP_Cut_Off], THEN "On Time"
IF 'UDS'[Unit]="PA", AND ('UDS'[Date_WO_Completed] <= RELATED(AIM_Schedule[PA_Cut_Off], THEN "On Time"
ELSE
"Late"

 

I hope I have provided enough information.

 

Thanks,

Andrea

Need Help

3 Replies

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    Hi Andreamary,

     

    You'd better unpivot table 'AIM_Schedule' to get below structure.

     

    Right click column [Attribute] and dumplicate it, then, split the new column by "_". Remove the unnecessary columns.

     

    Save and apply all above changes. Return back to table view. Establish a one to many relationship between two tables based on [Unit].

     

    Create a calculated column in 'UDS' table.

    Timeliness by Unit = IF(UDS[Date_WO_Completed]<=RELATED(AIM_Schedule[Value]),"On Time","Late")

     

    Best regards,

    Yuliana Gu

    • Andreamary's avatar
      Andreamary
      New Member
      8 years ago

      Hi Yuliana,

       

      Thanks so much for your clearly outlined solution! The AIM Schedule has a number of other fields, so as a result changing the structure of this table appears to be causing other issues which I don't have the skillset (yet) to fix. Is a solution possible through a more complex DAX code that maintains the original structure of the AIM Schedule, or would that be not possible/clumsy/inadvisable? 

       

      Cheers,

      Andrea

      • v-yulgu-msft's avatar
        v-yulgu-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        8 years ago

        Hi Andreamary,

         

        If we don't change the structure of 'AIM_Schedule', we cannot create any relationship between 'AIM_Schedule' and 'UDS'. Then, there is no way to refer to deadline values in 'UDS'. 

         

        As a workaround, you could duplicate the 'AIM_Schedule' table first, keep the structure of original table so that it can be used in other scenario. Then unpivot the duplicated table and create relationship between this duplicated table and 'UDS'.

         

        Regards,

        Yuliana Gu