topic Re: MAX function with Text column doesn't work in Live Connection SSAS Tabular in DAX Commands and Tips

MAX function with Text column doesn't work in Live Connection SSAS Tabular

Anonymous — Fri, 08 May 2020 14:44:59 GMT

Hi Experts,

I have a trouble using DAX function: MAX.

I want to get a unique value from Text column to use switch to use different calculation for each category in one measure.

In Power BI Desktop not using SSAS Tabular, I could make MAX function with Text column.

e.g. Measure = IF(MAX('Table'[TextColumn]) = "A"), Value, Blank())

The Table looks like this.

Category	Date	Value
A	2019-01-03	100
B	2019-01-03	80
C	2020-01-03	140
A	2020-01-04	570
B	2020-01-04	130
C	2020-01-04	330

However, whenever I use the same function in SSAS Tabular, it makes error with message like this.

"the function max takes an argument that evaluates to numbers or dates dax... "

I have used MAX function to get unique value from text column in DAX function, but I'm stuck in trouble because it doesn't work in SSAS Tabular.

Please give me any idea to solve this issue.

Thanks.

Re: MAX function with Text column doesn't work in Live Connection SSAS Tabular

Anonymous — Fri, 08 May 2020 14:50:17 GMT

the way you wrote it, you perhaps tried to do this instead

e.g. Measure = IF(MAX('Table'[TextColumn]) = "A", Value, Blank())

there was an extra ")"

don't know if it helps

Re: MAX function with Text column doesn't work in Live Connection SSAS Tabular

Greg_Deckler — Fri, 08 May 2020 14:53:14 GMT

Perhaps LASTNONBLANK ?

That's good to know about the MAX function because I use it all the time with text columns.

Re: MAX function with Text column doesn't work in Live Connection SSAS Tabular

Anonymous — Fri, 08 May 2020 14:54:39 GMT

I'm sorry I put wrong express in my post, I surely used correct expression that works in Power BI without SSAS.

like you corrected.

e.g. Measure = IF(MAX('Table'[TextColumn]) = "A", Value, Blank())

Therefore, the error is not from ")"

Thanks.

Re: MAX function with Text column doesn't work in Live Connection SSAS Tabular

Anonymous — Fri, 08 May 2020 14:57:47 GMT

Thanks Greg,

Have you usually used MAX function in Power BI like in my situation as well?

Did you try the same function in SSAS Tabular(SSDT) to make measure in it?

When Power BI is connected with SSAS Tabular is the same, it doesn't work MAX function even I make report level measure in Power BI.

Re: MAX function with Text column doesn't work in Live Connection SSAS Tabular

Greg_Deckler — Fri, 08 May 2020 15:02:08 GMT

No, generally not with SSAS Tabular, just in Power BI Desktop against an imported data set. Although, that is supposed to be SSAS Tabular under the hood so ?

Very strange.

Re: MAX function with Text column doesn't work in Live Connection SSAS Tabular

Anonymous — Fri, 08 May 2020 15:09:56 GMT

Yes, It is very strange.

I cannot figure out any clue at all that can make different work.

Re: MAX function with Text column doesn't work in Live Connection SSAS Tabular

Anonymous — Mon, 11 May 2020 00:14:07 GMT

Could you please review below DAX expression.

TEST_:=
IF(LASTNONBLANK('FactPayment'[Category], "SUM_PAY_AMT_KRW") , [Payment_KRW (AS-IS)],BLANK())

It makes error in SSAS tabular.

Thanks in advance.

Re: MAX function with Text column doesn't work in Live Connection SSAS Tabular

Anonymous — Wed, 13 May 2020 09:59:14 GMT

Hi Anonymous ,

Based on my test using Power Bi version : 2.80.5803.1061 64-bit .I cannot reproduce your issue here. Could you please update your Power bi desktop and check again?

Or we can try this formula.

IF(value('Table'[TextColumn]) = "A", Value, Blank())

Re: MAX function with Text column doesn't work in Live Connection SSAS Tabular

Anonymous — Wed, 13 May 2020 12:07:40 GMT

If you are sure that in the context there'll be only one value anyway... why not use SELECTEDVALUE?

Best
D

Re: MAX function with Text column doesn't work in Live Connection SSAS Tabular

Anonymous — Wed, 13 May 2020 15:30:06 GMT

Thanks for your kind reproduce with image, Frank.

However my issue is when I'm working with SSAS live connect, not just Power BI alone.

The same DAX expression works in Power BI, but when the data comes from SSAS tabular mode in Live connection, the expression makes error.

Looks weird.

Re: MAX function with Text column doesn't work in Live Connection SSAS Tabular

Anonymous — Wed, 13 May 2020 15:40:19 GMT

Anonymous

Do you have experience that worked the SELECTEDVALUE function in SSAS?

Do you mean my expression:

TEST_:=
IF(MAX('FactPayment'[Category], "SUM_PAY_AMT_KRW") , [Payment_KRW (AS-IS)], BLANK())

change into like below...?

IF(SELECTEDVALUE('FactPayment'[Category], "SUM_PAY_AMT_KRW") , [Payment_KRW (AS-IS)], BLANK())

I do not have any knowledge with the function, if my expression with SELECTEDVALUE is wrong, please correct it.

I tried MAX, LASTNONBLANK, FIRSTNONBLANK... but all of the function made error in SSAS...

Re: MAX function with Text column doesn't work in Live Connection SSAS Tabular

Anonymous — Wed, 13 May 2020 18:25:30 GMT

Whether you can use this function or not depends on the version of the product you use. Please find the documentation and examples of usage here:

https://dax.guide/selectedvalue/

Best
D

Re: MAX function with Text column doesn't work in Live Connection SSAS Tabular

adavbn3 — Tue, 26 Oct 2021 15:04:24 GMT

did you ever find a solution to this? running into the exact problem now, and can't think of a way to workaround it