February 3, 2020 at 12:23 pm
All,
I'm new to DAX and trying to improve my knowledge. The following code works:
EVALUATE
ADDCOLUMNS(
Posts,"TESTCOL",Year(Posts[CreationDate])
)
The following also works:
EVALUATE
FILTER (POSTS,
AND(
AND (Posts[FavoriteCount]>1,Posts[ViewCount]>10)
,SEARCH ("TEST",Posts[Tags],1,0)>0)
)
I'm trying to combine them and, unfortunately, haven't been successful. The following gives the error "The end of the input is reached.":
EVALUATE
ADDCOLUMNS(
Posts,"TESTCOL",Year(Posts[CreationDate])
,
FILTER (POSTS,
AND(
AND (Posts[FavoriteCount]>1,Posts[ViewCount]>10)
,SEARCH ("TEST",Posts[Tags],1,0)>0)
)
I'm not sure if it's a basic syntax mistake (I did check for brackets e.t.c) or I'm fundamentally wrong in the approach I'm taking to combining functions? If there are some articles online that it would be useful for me to read I'm happy to do that, I searched but didn't find anything.
Also if there is a correct way to post DAX queries that I've missed (similar to providing DDL and DML for SQL queries) then I'm happy to be advised.
Thanks
February 4, 2020 at 1:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
February 4, 2020 at 3:48 pm
You are missing the closing parentheses for the ADDCOLUMNS function
😎
February 4, 2020 at 6:01 pm
Thank you. Sorry I checked the syntax a few times, before posting, but missed that. Unfortunately I'm still struggling to combine the add columns and filter.
The AddColumns on it's own parses:
EVALUATE
ADDCOLUMNS(
Posts,"TESTCOL",Year(Posts[CreationDate])
)
The filter on it's own (I simplified the filter for now) also parses:
EVALUATE
FILTER (POSTS,
AND (Posts[FavoriteCount]>1,Posts[ViewCount]>10)
)
However my attempt at the combination doesn't:
EVALUATE
ADDCOLUMNS(
Posts,"TESTCOL",Year(Posts[CreationDate])
)
,
FILTER (POSTS,
AND (Posts[FavoriteCount]>1,Posts[ViewCount]>10)
)
February 4, 2020 at 6:46 pm
I think I've solved it. I need to do some more testing and then, if it works, I'll post in case it helps others.
February 7, 2020 at 10:31 am
This seems to work:
EVALUATE
FILTER (ADDCOLUMNS(
Posts,"Created year",Year(Posts[CreationDate])
),
AND(
AND (Posts[FavoriteCount]>1,Posts[ViewCount]>10)
,SEARCH ("SQL",Posts[Tags],1,0)>0)
)
The logic is that the first parameter of filter can either be a table or a filter or, as in this case, an expression that results in a table.
February 7, 2020 at 3:08 pm
If you think about DAX like a programming language, the functions either return scalar values or tables. And the functions in DAX receive either table-type objects or scalar values too. So anywhere a function "receives" a table, you can use an other function that returns a table. So instead of
COUNTROWS ( 'MyTable' ), you could do
COUNTROWS ( FILTER ( 'MyTable', 'MyTable'[Color] = 'blue' ) )
because FILTER returns a table. Once you get your head around that, DAX makes a bit more sense.
February 7, 2020 at 5:38 pm
Thank you for your advice.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply