topic Re: Lookup date value applied on filter not working in DAX Commands and Tips

Lookup date value applied on filter not working

meowster — Thu, 10 Aug 2023 05:26:13 GMT

Hello,

I am trying to use a date value from lookup as a variable for a filter but it doesn't seem to work. My DAX formula is shown as below:

Table =

VAR SelectedSalesDate = LOOKUPVALUE(

'DIM_SalesDate'[Last day of sales month],

'DIM_SalesDate'[DateText YYYY-MM],

SELECTEDVALUE('DIM_SalesDate'[DateText YYYY-MM])

)

RETURN

FILTER('FACT_Main',

'FACT_Main'[Last day of sales month] >= SelectedSalesDate &&

'FACT_Main'[Application Date] <= SelectedSalesDate &&

)

The above DAX does not return any syntax error and returns a blank table instead. Did this happen because the lookup value is not recognised as a date value? How can I fix this? Thank you!

Re: Lookup date value applied on filter not working

johnt75 — Thu, 10 Aug 2023 08:45:22 GMT

You can't use SELECTEDVALUE when creating a table. Calculated tables are only calculated during data load or refresh, so they don't pay any attention to slicers or filters. If you need something to react dynamically to filters or slicers you need to do it in a measure.

Re: Lookup date value applied on filter not working

meowster — Thu, 10 Aug 2023 10:07:28 GMT

Thank you johnt75 for this info. Didn't know about this. I've managed to find a workaround by summarizing the data into another table and then apply the filters from there.