Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Gdibbs
Helper I
Helper I

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!

 

Gdibbs_0-1633141630361.png

= Table.AddColumn(#"Filtered Rows", "Custom", each if [[Plant]] = "AS5A" and ([[#"Oct-21"]] < 1) then ([#"Oct-21"] * -1) else "")

 

Gdibbs_2-1633141684692.png

 

1 ACCEPTED SOLUTION
AllisonKennedy
Super User
Super User

@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.


Please @mention me in your reply if you want a response.

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

View solution in original post

8 REPLIES 8
heaher_iTalent
Administrator
Administrator

@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

v-kelly-msft
Community Support
Community Support

Hi  @Gdibbs ,

 

Is your issue solved now?

 

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

v-kelly-msft
Community Support
Community Support

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!

AllisonKennedy
Super User
Super User

@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.


Please @mention me in your reply if you want a response.

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

@AllisonKennedy  

 

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

 

Gdibbs_0-1633176457713.png

 

Gdibbs_1-1633176750578.png

 

 

@Gdibbs 

 

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)

 


Please @mention me in your reply if you want a response.

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

@AllisonKennedy 

 

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.


Please @mention me in your reply if you want a response.

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.