Forum Discussion

Shineccx007's avatar
Shineccx007
Helper II
2 years ago
Solved

POWER BI DAX CONDITIONAL CALCULATION ISSUE

Hello,

 

I am quite confusing why my DAX cannot recognize some columns when I have the conditional calculation formula among four three tables.

 

Table "ItemCard" include column [Item No] from 1 to 30, column [Category] include "Set" and "CAL",column [Lead time] as numbers,
Table "ItemLedgerEntries" include column [Item No] from 1 to 30, column [qty], measure [avgDailyConsumptionQty]

Table "SetToOrder" include column [Item No] from1 to 10, column [SafetyStockQty], column [Category] all is "Set",

 

Now I want to write the DAX to enable me know all the item Safety Stock Qty so I can know when shall I place new order for the items.

Logic:
SafetyQty = 
IF(ItemCard[Category]="Set",
    SetToOrder[SafetyStockQty],
    ItemCard[LeadTime]*ItemLedgerEntries[AvgDailyConsumptionQty]*1.8))

 

I don't know why for some reason, the system prompt my DAX logic is wrong. Power BI desktop cannot execujte this DAX.

 

Can anyone offer some suggestions?

 

Thank you!

11 Replies

  • Fowmy's avatar
    Fowmy
    Super User
    2 years ago

    Shineccx007 

    Have you created the relationships between the tables, pleas share a screenshot of it. In which table you want the new calucalted column to calculate the Safty Stock? 

    • Shineccx007's avatar
      Shineccx007
      Helper II
      2 years ago

      Hi Fowmy,

       

      Relationship among these tables:

      The key is item No

      ItemCard is linked by [item no] to ItemLedgerEntries, one to many

      ItemCard is linked by [item no] to SetToOrder, one to one

      I wanted to add the calculated column under ItemCard table on the SafetyStock Calculation

       

      Thanks!

      • Fowmy's avatar
        Fowmy
        Super User
        2 years ago

        Shineccx007 

        I am not sure the following will work as I need to know how your measure [AvgDailyConsumptionQty]   is calculated. 

        Please try:

        SafetyQty =
IF (
    ItemCard[Category] = "Set",
    RELATED ( SetToOrder[SafetyStockQty] ),
    ItemCard[LeadTime] * [AvgDailyConsumptionQty] * 1.8
)