Flawed IF Statement Logic | Need Help

[safifaruqi]

Hi Guys, 

I am trying to create a forecasting sheet. The objective is simple I would like to see how my inventory levels look based on which I will be using this tool to recommend how much inventory to purhcase. 

I have created a calculated column for this task , with the following DAX code: 

Open_PO_Release_Qty_12M_Measure 2 = if('Dimension Item'[Open_PO_Release_Status_12M]="Release Flag 12M",IF('Dimension Item'[Absolute_In_Stock_Forecast_12M]>[OpenPO_Qty_Measure],SUM('data purch'[openqty]),ABS('Dimension Item'[In_Stock_Qty_Forecast_12M])))

The math is simple, if absolute in stock value > Open PO Qty, then display Open Qty  value (55 in this example) otherwise, aboslute in stock value.

Now, this part of the formula is where the issue is stemming from, IF('Dimension Item'[Absolute_In_Stock_Forecast_12M]>[OpenPO_Qty_Measure] for some reason dax always finds it to be false and I dont know why Open Qty Measure is summing up to be larger than the absolutel forecast. I am fairly confident that its missing some context and the summed value is large but when I throw it on the table visual i dont see anything. 

OpenPO_Qty_Measure = sum('data purch'[openqty])

I am calculating this for each and every SKU, the end goal is to filter out the SKU list for which we can buy invnetory to avoid out of stock situation. 

This should be a quick fix, I just cant seem to figure out what I am doing wrong.

Any help is super appreciated. 

[v-shex-msft]

Hi @safifaruqi,

Calculate column and measure are worked different data levels. Current power bi does not support creating dynamic calculated column/table based on filter effects. (these dynamic parts can be achieved in measure, but this calculation will be fixed if you use the measure formula in calculate column)

BTW, you can take a look at the flowing blog to know more about the difference between the calculated column and the measure formula.

Calculated Columns and Measures in DAX - SQLBI

Notice: the data level of power bi.

Database(external) -> query table(query, custom function, query parameters) -> data model table(table, calculate column/table) -> data view with virtual tables(measure, visual, filter, slicer)

Regards,
Xiaoxin Sheng

[safifaruqi]

@amitchandak hi amit, tagging you here as I think this a quick fix and feel like its a conceptual mistake being made here by me. Do you think you can afford 10 minutes to point me in the right direction, time permitting ofcourse. 

[HotChilli]

OK, it looks like the formula is using measures which is not good practice so I advise rewriting the column logic to avoid measures.  You'll be able to work out any context from there.

---

After that, it is really difficult to debug from this side without seeing a model and the relationships between tables so if you're not getting anywhere after a rewrite, I advise posting a pbix with test data and someone will help.

[safifaruqi]

I did that, now everything within is a column, calc or otherwise and I am still getting a weird output. 

the issue is that this portion of the logic is failing : 

IF(-('Dimension Item'[In_Stock_Qty_Forecast_12M])>SUM('data purch'[openqty]),

            SUM('data purch'[openqty]),-('Dimension Item'[In_Stock_Qty_Forecast_12M])))

Where : -('Dimension Item'[In_Stock_Qty_Forecast_12M]) is a column 

the issue is mostly of the first argument being true but dax considering it to be correct. I am guessing its summing the whole column and not considering the row level context which is making the first argument true hence the wrong output. 

I just dont know how to fix it yet. 

the column logic for this column is : 

In_Stock_Qty_Forecast_12M = 'Dimension Item'[in stock]-'Dimension Item'[Consumption_12M_Col]

sometimes dax finds

[HotChilli]

Is this really a calculated column?

[safifaruqi]

thank you for the reply, yes it is.