topic If Condition for Text not recognizing column name in DAX Commands and Tips

If Condition for Text not recognizing column name

Anonymous — Sat, 20 Nov 2021 18:51:32 GMT

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 aggregate function". How do I apply aggregate function on text column? I need to work in DirectQuery mode only.

Re: If Condition for Text not recognizing column name

VahidDM — Sat, 20 Nov 2021 13:55:09 GMT

Hi Anonymous

Use MAX(Sessions[column name] in the measure.

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/

Re: If Condition for Text not recognizing column name

Anonymous — Sat, 20 Nov 2021 18:53:06 GMT

Hello VahidDM,

Thank you for your response, but when I try putting aggreagte function like MAX(Sessions[column name]) I get error 'can not convert value of type text to Number'.

I need to work in DirectQuery mode only.

I believe to apply aggregate function it needs to be numberic data, however my column contains text.

Re: If Condition for Text not recognizing column name

jeroendekk — Sat, 20 Nov 2021 22:44:43 GMT

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

Re: If Condition for Text not recognizing column name

VahidDM — Sat, 20 Nov 2021 22:56:13 GMT

jeroendekk

FYI:

😎

Re: If Condition for Text not recognizing column name

Anonymous — Mon, 22 Nov 2021 18:08:58 GMT

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:

Re: If Condition for Text not recognizing column name

v-robertq-msft — Tue, 23 Nov 2021 09:13:33 GMT

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.

Re: If Condition for Text not recognizing column name

jeroendekk — Tue, 23 Nov 2021 09:40:10 GMT

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

Re: If Condition for Text not recognizing column name

VahidDM — Tue, 23 Nov 2021 23:07:44 GMT

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/

Re: If Condition for Text not recognizing column name

v-robertq-msft — Wed, 24 Nov 2021 03:43:45 GMT

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.