DAX combining functions

  • as1981

    SSCrazy

    Points: 2617

    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

     

  • Site Owners

    SSC Guru

    Points: 80378

    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: 182410

    You are missing the closing parentheses for the ADDCOLUMNS function

    😎

     

  • as1981

    SSCrazy

    Points: 2617

    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)


    )
  • as1981

    SSCrazy

    Points: 2617

    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.

     

  • as1981

    SSCrazy

    Points: 2617

    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: 62674

    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.

  • as1981

    SSCrazy

    Points: 2617

    Thank you for your advice.

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

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