Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 "")
Solved! Go to Solution.
@Gdibbs , a couple of things:
1) M is case sensitive and spelling counts. You've written two different things:
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.
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
@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 ,
Is your issue solved now?
Best Regards,
Kelly
Did I answer your question? Mark my reply as a solution!
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,
Kelly
Did 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:
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.
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
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)
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
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.
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
118 | |
101 | |
71 | |
61 |