Unable to filter by a VAR value

[Anonymous]

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? 

[Anonymous]

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]

Doens't work

Here is a link to the pbix if someone wants to look at it:
https://www.dropbox.com/s/39hlb33wfjbu6a7/VarProblem.pbix?dl=0

[v-shex-msft]

Hi @Anonymous,

Current variable table not support to use 'variable Name'[Column Name], it only works on static tables.

For your scenario, you can try to use below formula to filter date by today function.

New Table =
VAR nDayIndex =
    CALCULATETABLE (
        VALUES ( Dates[Index] ),
        FILTER ( ALL ( Dates ), [Date] = TODAY () )
    )
RETURN
    FILTER ( Dates, CONTAINS ( nDayIndex, Dates[Index], [Index] ) )

Regards,

Xiaoxin Sheng

[Anonymous · Accepted solution]

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