Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

If Condition for Text not recognizing column name

I have a text column USERID for which I want to determine part of a USERID based on IF condition. However, I tried as both column and measure, but getting error "can not find column, please use aggre...
  • v-robertq-msft's avatar
    v-robertq-msft
    4 years ago

    Hi, Anonymous 

    According to your DAX formula and error message, it seems that the problem occurs because of one missing parenthesis in the function LEN(MAX('Sessions'[Known Visitor Id]):

     

    I suggest you to change the DAX formula like this and check if the problem can disappear:

    Known Visitor Id = 

RIGHT(MAX('Sessions'[Known Visitor Id]),LEN(MAX('Sessions'[Known Visitor Id])) - FIND("\",MAX('Sessions'[Known Visitor Id])))

     

    And you can get what you want.

     

    If you still have a problem, you can post some sample data(without sensitive data) and your expected result.

    How to Get Your Question Answered Quickly 

    Thank you very much!

     

    Best Regards,

    Community Support Team _Robert Qin

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

jeroendekk's avatar
jeroendekk
Responsive Resident
4 years ago

Hi Anonymous 
Indeed MAX will not work with text. I am not sure about the expected result but you could try FIRSTNONBLANKVALUE, which is an aggregation that works on text columns and gives (like the name implies) the first value.

Best regards,

 

Jeroen

  • jeroendekk's avatar
    jeroendekk
    Responsive Resident
    4 years ago

    Oops sorry, apparently you can. Learned something new thanks!

    • v-robertq-msft's avatar
      v-robertq-msft
      Community Support
      4 years ago

      Hi, Anonymous 

      Glad to hear that you have solved your problem with the help of VahidDM, would you like to mark his reply as a solution so that others can learn from it too?

      Thanks in advance!

      How to Get Your Question Answered Quickly 

       

      Best Regards,

      Community Support Team _Robert Qin

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    VahidDM 

    I tried 

    Known Visitor Id = RIGHT(MAX('Sessions'[Known Visitor Id]),LEN(MAX('Sessions'[Known Visitor Id]) - FIND("\",MAX('Sessions'[Known Visitor Id]))))
     
    However I am getting error: 

     

     

    • v-robertq-msft's avatar
      v-robertq-msft
      Community Support
      4 years ago

      Hi, Anonymous 

      According to your DAX formula and error message, it seems that the problem occurs because of one missing parenthesis in the function LEN(MAX('Sessions'[Known Visitor Id]):

       

      I suggest you to change the DAX formula like this and check if the problem can disappear:

      Known Visitor Id = 

RIGHT(MAX('Sessions'[Known Visitor Id]),LEN(MAX('Sessions'[Known Visitor Id])) - FIND("\",MAX('Sessions'[Known Visitor Id])))

       

      And you can get what you want.

       

      If you still have a problem, you can post some sample data(without sensitive data) and your expected result.

      How to Get Your Question Answered Quickly 

      Thank you very much!

       

      Best Regards,

      Community Support Team _Robert Qin

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • VahidDM's avatar
      VahidDM
      Super User
      4 years ago

      Hi Anonymous 

       

      Try this:

      Known Visitor Id =
RIGHT (
    MAX ( 'Sessions'[Known Visitor Id] ),
    LEN ( MAX ( 'Sessions'[Known Visitor Id] ) )
        - FIND ( "\", MAX ( 'Sessions'[Known Visitor Id] ) )
)

       

      If it doesn't work, then please share a sample of your data.

      If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
      Appreciate your Kudos!!
      LinkedIn:       www.linkedin.com/in/vahid-dm/