Add column in visual table that checks if row is before or after selected calendar date

[Anonymous]

Hi,

I have a problem where I'm trying to add a column to my visual table that checks if the last date for an id is before or after date I select in a slicer. This slicer does not filter the table because I would like to keep all rows, but I wan't a column that checks if the date selected in slicer is before or after row date. 

I have tried using selectedvalue to get this working, but it's not, Does anyone have a nice workaround for this?

Thanks!

[CNENFRNL]

Hi, @Anonymous , you may use a measure to record the selected date from the slicer

Date Selected = SelectedValue( Calendar[Calendar Date] )

then compare each row with the value of this measure.

[Anonymous]

Hi @CNENFRNL ,

No, this is not working, the screenshot you see, calendar date is using the selected value, then I use that in the "after calendar date"

Seems that since I don't filter the table, the selectedvalue is not filtered either..

[CNENFRNL]

Hi, @Anonymous , the screenshot seems ok for me 🤔

A simple means to debug is to put that measure in the viz to check which date on earth is used in the calculation for "after calendar date".

With an attachment of mockup file, your issue would be way much faster to get solved.

[Anonymous]

Hi @CNENFRNL 

I have uploaded a mock pbix file here: https://gofile.io/d/itKrHR

Since interaction with the table and my calendar slicer is off, not sure it would work to put it into the table. 

[CNENFRNL · Accepted solution]

Hi, @Anonymous , the cause of issue lies in the evaluation context of measures.

Each row of viz produces a filter context, i.e. ID=1, and this viz is separated from being filer by the slicer; then the only ID=1 is the only filter context for measure [calendar date], which evaluates to "9/17/2020".

A simple solution is to create a separate date table used as slicer and keep normal filters as well. Pls refer to attached file for more details.

[Anonymous]

Hi @CNENFRNL ,

Works great, thanks!