Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

Unable to filter by a VAR value

I’ve created a date table that has an índex colum sorted by date.

 

Ran the following tests:

EVALUATE
VAR nDayIndex =
    SELECTCOLUMNS ( FILTER ( Dates, Dates[Date] = TODAY () ), "Id", Dates[Index] )
RETURN
    nDayIndex

 

I was expecting the following code to work but it doesn't:

EVALUATE
VAR nDayIndex =
    SELECTCOLUMNS ( FILTER ( Dates, Dates[Date] = TODAY () ), "Id", Dates[Index] )
RETURN
    FILTER ( Dates, Dates[Index] = nDayIndex )

The error I'm getting is:

 

What intrigues me is that the following works:

EVALUATE
var nDay=601
return
FILTER(Dates,Dates[Index]=nDay)

 

Can anyone explain why it doesn't work? 

Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    Okay, maybe I did things a little to fast :) Sorry...

     

    But since the variable returns a table (with lineage to the original table) it should be possible to use it as a filter condition in for example a Calculatetable

     

    I tested this with your file and in returned the relevant row in the Dates table

     

    EVALUATE
    var nDayIndex = SELECTCOLUMNS(FILTER(Dates,Dates[Date] = TODAY()),"Id",Dates[Index])
    return
    CALCULATETABLE(Dates,nDayIndex)  

     

     

    Br,

    Magnus

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi,

     

    Your variable nDayIndex that is constructed from SELECTCOLUMNS will return a table with only one column an one row. To use that as a scalar value in a FILTER expression you need to refer to the actual value in the resulting table. The following example will give you the desired result.

     

    EVALUATE
VAR nDayIndex =
    SELECTCOLUMNS ( FILTER ( Dates, Dates[Date] = TODAY () ), "Id", Dates[Index] )
RETURN
    FILTER ( Dates, Dates[Index] = Values(nDayIndex[Id] )

     

    Br,

    Magnus

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        Okay, maybe I did things a little to fast :) Sorry...

         

        But since the variable returns a table (with lineage to the original table) it should be possible to use it as a filter condition in for example a Calculatetable

         

        I tested this with your file and in returned the relevant row in the Dates table

         

        EVALUATE
        var nDayIndex = SELECTCOLUMNS(FILTER(Dates,Dates[Date] = TODAY()),"Id",Dates[Index])
        return
        CALCULATETABLE(Dates,nDayIndex)  

         

         

        Br,

        Magnus