Forum Discussion

evalromf's avatar
evalromf
Helper I
9 years ago

AND IF in conditional column

Hi,   I'm trying to create a new column that should be populated with a certain number based on the contents of several other columns.   What I'm looking for is something like this:   If [COUNT...
Need Help
RodrigoTXRA's avatar
RodrigoTXRA
Helper I
8 years ago

HI All

 

Not sure this is the right topic for my issue, but I have an excel cell which can have by out of 4 consonants and they are represented as follows:


F = 70

P = 80
G = 100
E = 116

I'm trying to create a DAX new measure (Trying to bring this formula from excel into Power BI) which would look into the field and transform it into a number as per above.

Would anyone know how to do this?

 

Appreciate any feedback

Regards

Rodrigo

 



 

  • GilbertQ's avatar
    GilbertQ
    Super User
    8 years ago
    Hi there

    When you say create a DAX measure, what is the expected result from your data you posted?
    • RodrigoTXRA's avatar
      RodrigoTXRA
      Helper I
      8 years ago

      HI GilbertQ thanks for your reply.

       

      I basically need the measure to look into a field and if it finds one of those four letters, to transform it into the respective number.


      something like this

      Measure = IF("E", then "116", or "P", then "80", or "G" then "100", or "F" then "70"

       

      I just don't know the correct syntax in Power BI to perform the above.

       

      Appreciate your assistance

      Regards

      Rodrigo

      • GilbertQ's avatar
        GilbertQ
        Super User
        8 years ago
        Hi there

        If you need this to be a column I would suggest making the changes in the Power Query Editor, where it is a lot easier to make the changes you require.
        The reason being is that you can use functions like "contains" or looking for specific things.
    • RodrigoTXRA's avatar
      RodrigoTXRA
      Helper I
      8 years ago

      Hello again

      It think that it would be something like this formula below, but the other way around, instead of transforming numbers into text, for the month, it would transform text into numbers.

       

      =SWITCH([Month], 1, "January", 2, "February", 3, "March", 4, "April" , 5, "May", 6, "June", 7, "July", 8, "August" , 9, "September", 10, "October", 11, "November", 12, "December" , "Unknown month number" )

       

      Any ideas?

      Regards

      Rodrigo