Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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
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
Here is a link to the pbix if someone wants to look at it:
https://www.dropbox.com/s/39hlb33wfjbu6a7/VarProblem.pbix?dl=0
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |