Forum Discussion
Conditional Column in Dax not calculating as expected
Hi All,
I suspect I am missing something very basic and/or obvious.
I am trying to do the following...
If the column headered "plant" is equal to "US5A" and the month headered "Oct-21" is greated than zero, then I want DAX to multiply the positive value by negative one, else leave it as a null value.
Here is the last attempt I made, but as you can see in the "Custom" column I am not getting the negative value where I have a positive value in column "Oct-21"
Any guidance / support is greatly appreciated.
Many thanks!
= Table.AddColumn(#"Filtered Rows", "Custom", each if [[Plant]] = "AS5A" and ([[#"Oct-21"]] < 1) then ([#"Oct-21"] * -1) else "")
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.
8 Replies
v-kelly-msft I have unmarked this as the accepted solution, as it appears that AllisonKennedy & Gdibbs are in the process of working thru this.
Gdibbs please let us know if you need anything else, or if you have the solution that you need. Then please mark as "Accepted as Solution". Super Users in the community are here to assist you as well as Community Support. We just want to make sure that you are finding your answers here in the community.
Thank you for being part of the Power BI Community.
Best,
Heather H
Community Manager
Power BI
Hi Gdibbs ,
Try:
= Table.AddColumn(#"Filtered Rows", "Custom", each if Text.From([[Plant]]) = "AS5A" and (Number.From([[#"Oct-21"]] )< 1) then (Number.From([#"Oct-21" ]) * -1) else "")Best Regards,
KellyDid I answer your question? Mark my reply as a solution!
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.
Thank you for the feedback. Here are some bigger screenshots. For row 1 in column Oct-21 I have a positive value. For the positive value I would like the conditional column to return a value of -28733. For row two, because it's null I want it to be either zero or null. (I assume zero is more flexible)
Based on the conditional code I would expect to get that. Also, I checked the plant input and it should be US5A - shown in screenshot.
Thanks again for the help and patience. I am learning (as you can tell) and chaulk this up to growing pains.
Thanks
Greg
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)
Hi Gdibbs ,
Is your issue solved now?
Best Regards,
KellyDid I answer your question? Mark my reply as a solution!
