Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

If statement (greater than) different tables

Hi - 

 

Been pounding my head against this for weeks.. hopefully someone can help. I'm trying to calculate whether or not we've placed PO's at or before the required lead time.

 

I have 3 tables:

 

Vendor

PurchaseOrderHeader

PurchaseOrderDetail

 

 

The StandardLeadTime column is in the Vendor Table

 

and the Lead Time on PO column is a calculated colum in the PurchaseOrderDetail table

 

 

Lead Time on PO = DATEDIFF(RELATED(PO_PurchaseOrderHeader[DateCreated]),PO_PurchaseOrderDetail[UDF_ON_DOCK_ETA],DAY)
 
What I want to figure out, is how to make a caculated column that says IF the number in the Lead Time on PO (in the PurchaseOrderDetails Table) >= the number in the StandardLeadTime column (in the Vendor Table), "Yes", otherwise, "No"
 
I keep getting this
 
 
Need Help
  • v-lionel-msft's avatar
    v-lionel-msft
    6 years ago

    Hi Anonymous ,

     

    Model 1

    Result1

     

    Model2 

    Result2

    RELATED() 

     

    Try to create a measure like this.

    Measure 2 = 
VAR x = 
CALCULATE(
    SUM(Vendor[Value]),
    FILTER( Vendor, Vendor[ItemCode] = SELECTEDVALUE(Vendor[ItemCode]))
)
RETURN
IF(
    SELECTEDVALUE(PurchaseOrderDetail[Value]) >= x,
    "Yes", "No"
)

     

     

    Or Create a calculated column like this.

    Column 2 = 
VAR x = 
CALCULATE(
    SUM(Vendor[Value]),
    ALLEXCEPT(PurchaseOrderDetail, PurchaseOrderDetail[ItemCode])
)
RETURN
IF(
    PurchaseOrderDetail[Value] >= x,
    "yES", "nO"
)

     

    Best regards,
    Lionel Chen

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

4 Replies

  • d_gosbell's avatar
    d_gosbell
    Icon for Super User rankSuper User
    6 years ago
    Anonymous wrote:

     

    The StandardLeadTime column is in the Vendor Table

     

     
     

    Your relationship is going in the wrong direction to be able to use RELATED() against the vendor table. The way you have the relationships modelled is effectively saying that one purchase order line can be related to many different vendors. This does not sound correct to me.

     

    If you edit this relationship and change the cardinality from many-to-one to one-to-many  (or vis-versa) this will switch the From/To columns and the RELATED() function will then be able to function as you expect.

  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    6 years ago

    Is it a 1:many relationship between Vendor and OrderDetail tables?  Is itemcode the same data type on both sides of the relationship?   If so, Related should work. If not, you'll need to change it to that or use RELATEDTABLE() and an aggregation.

     

    Regards,

    Pat

     

  • v-lionel-msft's avatar
    v-lionel-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Anonymous ,

     

    Model 1

    Result1

     

    Model2 

    Result2

    RELATED() 

     

    Try to create a measure like this.

    Measure 2 = 
VAR x = 
CALCULATE(
    SUM(Vendor[Value]),
    FILTER( Vendor, Vendor[ItemCode] = SELECTEDVALUE(Vendor[ItemCode]))
)
RETURN
IF(
    SELECTEDVALUE(PurchaseOrderDetail[Value]) >= x,
    "Yes", "No"
)

     

     

    Or Create a calculated column like this.

    Column 2 = 
VAR x = 
CALCULATE(
    SUM(Vendor[Value]),
    ALLEXCEPT(PurchaseOrderDetail, PurchaseOrderDetail[ItemCode])
)
RETURN
IF(
    PurchaseOrderDetail[Value] >= x,
    "yES", "nO"
)

     

    Best regards,
    Lionel Chen

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      This worked! Your model 2 calculated column worked. Thank you!