DAX combining functions

Question

Post reply

DAX combining functions

as_1234

SSCrazy

Points: 2859
More actions
February 3, 2020 at 12:23 pm

#3722552
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

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Site Owners SSC Guru Points: 80330 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 2

You are missing the closing parentheses for the ADDCOLUMNS function

😎

as_1234 SSCrazy Points: 2859 More actions · Answer 3

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)


)

as_1234 SSCrazy Points: 2859 More actions · Answer 4

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.

as_1234 SSCrazy Points: 2859 More actions · Answer 5

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.

pietlinden SSC Guru Points: 63503 More actions · Answer 6

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.

as_1234 SSCrazy Points: 2859 More actions · Answer 7

as_1234

SSCrazy

Points: 2859

February 7, 2020 at 5:38 pm

#3724019

Thank you for your advice.