topic Re: POWER BI DAX CONDITIONAL CALCULATION ISSUE in DAX Commands and Tips

POWER BI DAX CONDITIONAL CALCULATION ISSUE

Shineccx007 — Sun, 14 Jan 2024 05:17:24 GMT

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!

Re: POWER BI DAX CONDITIONAL CALCULATION ISSUE

Fowmy — Sun, 14 Jan 2024 07:28:06 GMT

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?

Re: POWER BI DAX CONDITIONAL CALCULATION ISSUE

Shineccx007 — Sun, 14 Jan 2024 14:21:30 GMT

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!

Re: POWER BI DAX CONDITIONAL CALCULATION ISSUE

Fowmy — Sun, 14 Jan 2024 14:35:26 GMT

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 )

Re: POWER BI DAX CONDITIONAL CALCULATION ISSUE

Shineccx007 — Sun, 14 Jan 2024 15:39:14 GMT

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:

Re: POWER BI DAX CONDITIONAL CALCULATION ISSUE

Fowmy — Sun, 14 Jan 2024 19:55:02 GMT

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.

Re: POWER BI DAX CONDITIONAL CALCULATION ISSUE

Shineccx007 — Sun, 14 Jan 2024 21:12:14 GMT

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

Re: POWER BI DAX CONDITIONAL CALCULATION ISSUE

Shineccx007 — Sun, 14 Jan 2024 21:24:19 GMT

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

Re: POWER BI DAX CONDITIONAL CALCULATION ISSUE

Shineccx007 — Sun, 14 Jan 2024 22:39:13 GMT

VJN

Anonymous

Can you help have a look at it? Thanks!

Re: POWER BI DAX CONDITIONAL CALCULATION ISSUE

Fowmy — Mon, 15 Jan 2024 06:46:13 GMT

Shineccx007

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

Re: POWER BI DAX CONDITIONAL CALCULATION ISSUE

Shineccx007 — Mon, 15 Jan 2024 16:35:50 GMT

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.

Re: POWER BI DAX CONDITIONAL CALCULATION ISSUE

Shineccx007 — Mon, 15 Jan 2024 16:36:02 GMT

Fowmy