cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
safifaruqi
Frequent Visitor

Flawed IF Statement Logic | Need Help

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. 
 
safifaruqi_0-1626288909756.png

 

 

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. 

6 REPLIES 6
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
safifaruqi
Frequent Visitor

@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
Super User
Super User

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.

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
Super User
Super User

Is this really a calculated column?

thank you for the reply, yes it is. 

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors