New column needed with calculated value on a numeric field based on non-numeric column value

[Anonymous]

I'm trying create a column using a numeric field based on the value of a non-numeric column.  I'm using An IF Statement as follows:]

TOD = IF( AND('Table'[AM-PM] = "PM", 'Table'[Hour] > 12), ('Table'[Hour] + 12), 'Table'[Hour]).

So if the value found in the AM-PM column is equal to PM, then add 12 to the hour if it's greater than 12.  When I do this, nothing happens.  Any suggestions?  And yes, I will also need to do another statement for AM values equal to 12 to make them zero (0).  But first things first.  Any suggestions?  

[Greg_Deckler]

@Anonymous Strange. Worked flawlessly for me. See attached PBIX below signature. You should at least get the original Hour column values. I would also recommend switching over to using a SWITCH(TRUE(),...) statement to avoid nested IF statements.

[Anonymous]

Greetings again, Greg!  Was finally able to get the results that I needed.  It came from a colleague where I work.  First off, there were several issues with my data:

1. There was a space before the AM and PM data.
2. I had the comparison operator set to greater than, when it should have been less than.
3. The final solution required a multiple step process (which I expected), but it needed to be broken down into 4 steps, as follows:

Providing the final result:

I hope this helps anyone else that may need such a solution.

Greg, thanks again for giving it a go!

Best regards,

Marty

[Anonymous]

Greetings and Happy St. Patty's Day, Greg.  So I created a test data sample of my own and put it into PowerBI and was not able to get it to work.  Unfortunately, I don't have the option to attach a file or I would include the .xlsx test data and .pbix file.  Here's a screen shot of the data in the .xlsx file:

and a screen shot of the table in .pbix:

Associated DAX Code:

TOD1 = IF(AND('Test Data'[Hour Data] > 12, 'Test Data'[AM-PM] = "PM"),'Test Data'[Hour Data] + 12, 'Test Data'[Hour Data])

and 

TOD2 = IF(AND('Test Data'[Hour] > 12, 'Test Data'[AM-PM] = "PM"),'Test Data'[Hour] + 12, 'Test Data'[Hour])

Still not working.  Have no idea why.  Unlikely to work even using SWITCH (TRUE() ...) if this doesn't work.

[Anonymous]

Here's the results I get:

😞

[Anonymous]

Yes, very interesting.  It's not working for me at all.  I also tried using SWITCH to not have to do two separate columns and IF statements, but that also didn't work.  When I tried to open your file, it says I'm using an old version of Power BI and need to update.  That may be the problem right there.  Will update and retry and will let you know.  Thanks for your help, Greg!