Getting a selected date value from an unrelated table.

[GlynMThomas]

Hi,

I have an agent table with a date column which give me the date the agent left our company. This table has no direct relation to my date table in power bi. I also have a drop down filter connected to the date table, this is connected to a text column on that table that gives all the dates unless it is the most recent date, then it says current (this was to get around the problem in power bi where you cannot default a filter to the most recent date).

I'm trying to pull the currently selected date through into my agent table so I can then compare the current date to the leaving date of the agent and flag whether they were employed or not on that date. I have tried using SELECTEDVALUE() and MIN()/MAX() on the date column to get this date, unfortunately SELECTEDVALUE() comes back blank, and MIN/MAX just select the min or max of the whole date table even though it's being filtered. How do I get the selected date from the date table into my agent table when there's no direct relationship?

[GlynMThomas · Accepted solution]

I figured this out, it was because I was using a column and not a measure. I created a max date measure then used that to apply the filter using another measure which filtered the dates correctly.

Max Date = CALCULATE(MAX(Dates[Date]),ALLSELECTED(Dates))

Still Employed = IF(IF(MAX(Logins[Leave Date]) = BLANK(), [Max Date], MAX(Logins[Leave Date])) >= [Max Date], 1, 0)

[v-luwang-msft]

Hi @GlynMThomas ，

Please refer to the following article and if the problem persists，could you pls share your pbix file?Remember to remove condidential data.

Use a slicer to filter an unrelated table using a mock CONTAINSX calculation

Get values from unrelated table.

How to get a Date Value from unrelated tables

Wish it is helpful for you!

Best Regards

Lucien