Forum Discussion
Conditional Column in Dax not calculating as expected
Gdibbs , a couple of things:
1) M is case sensitive and spelling counts. You've written two different things:
- [Plant]] = "AS5A"
- "plant" is equal to "US5A"
Which option do you want? A or U?
2) It's difficult to see with your screenshots, as you haven't included the columns in one shot, but the example you did show does not appear to have any data < 1, so that will leave everything as blank (not null - if you want null you need to specify that in your column instead of the empty quotes "").
= Table.AddColumn(#"Filtered Rows", "Custom", each if [[Plant]] = "AS5A" and ([[#"Oct-21"]] < 1) then ([#"Oct-21"] * -1) else null)Otherwise I can't see any obvious errors at a quick glance.
You have a couple of options, depending what you're trying to do - are there any negative numbers or 0? 0 and null behave very differently, especially with averages, so you need to know which one you want.
I recommend keeping null as null generally but I can't say for sure without knowing more about your data. Your condition is looking for value <1, if you change that to value >0, then it will convert all positive numbers to negative values.
Try updating to this formula below:
= Table.AddColumn(#"Filtered Rows", "Custom", each if [[Plant]] = "AS5A" and ([[#"Oct-21"]] > 0) then ([#"Oct-21"] * -1) else null)
Thanks. I used the formula you provided, but it returned null for every row where the plant was identified as Oct-21 and when the value in the row was a positive value greater than zero. I converted all "null" values to zero in the column and it still did not work.
The data I am using was uploaded via excel and is standard data.
Any ideas? For some reason, it seems, as though DAX is not recognizing the positive values as values.
Should I try to import some basic excel data I make up to mimic the objective to see if it works?
Thanks.
Gdibbs What is the data type of the Oct-21 column? It needs to be number first - see if that helps.