Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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

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?  
Need Help

5 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    3 years ago

    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's avatar
      Anonymous
      Not applicable
      3 years ago

      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!

       

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Here's the results I get:

      😞

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      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.