POWER BI DAX CONDITIONAL CALCULATION ISSUE

[Shineccx007]

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!

[Fowmy]

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

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]

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

[Shineccx007]

Thank you Fowny!

My original dataset, under ItemCard, the column [lead time] is missing some data there. I write the dax below, let the system calculate the one with value only. But I have no idea why it kept showing blank in my measurement. Screenshot as below.

[Shineccx007]

@Fowmy 

[Shineccx007]

Hi Fowemy @Fowmy 

It showed "Expressions that yield variant data-type cannot be used to define calculated columns."

Avg Daily consumption column under table ItemLedgerEntries is calculated by summarize recent 3 month consumption quantity and divide by 20.

I have a seperate date table "dim_date", it's a calendar from min.ItemLedgerEntries[PostingDate] till today, linked by date to ItemLedgerEntries.

Model as below:

[Fowmy]

@Shineccx007 

Looks like i meed to have a look at your model.
Sharing a dummy Power BI file representing your scenario would be beneficial. You can save the Power BI file on Google Drive or any other cloud storage platform and provide the link here. Kindly ensure that permission is granted to open the file.

[Shineccx007]

@Fowmy 

Here is the dataset I modified. Please have a look at it.

Thank you so much! 

https://drive.google.com/file/d/1V_bO2Zj5eF-zT4CkTa7o1FV3m9pFxcBb/view?usp=sharing

[Shineccx007]

@Fowmy 

Here is the link on the file. I modified the dataset a little bit.

https://drive.google.com/file/d/1baq1qiV36AaJqAQnVIFNA4gsO4yVJP8v/view?usp=sharing

[Fowmy · Accepted solution]

@Shineccx007 

Please check the attached file and see if this is your requirement.

[Shineccx007]

@VJN 

@v-jialongy-msft 

Can you help have a look at it? Thanks!