Issue in using "selectedvalue" function for filtering another table

[asadnsit]

I am facing issue in using value selected in a date slicer to filter another table:

Table1: Employee master 

Table2: Calculated date table created using calendar function

Now I have put a slicer in my dashboard using Table2 (Only single value selection allowed)

Selected value is appearing fine in display card using "selectedvalue" function

DAX query for measure :

Selected_date = DATEVALUE(SELECTEDVALUE(Date_Table[Date]))

Now I am creating calculated table by filtering Table 1 using value selected in slicer:

DAX code:

Emp_master_filtered =

CALCULATETABLE

( Emp_Master,

Emp_Master[DOJ]<=DATEVALUE(SELECTEDVALUE(Date_Table[Date])),

Emp_Master[DOR] >= DATEVALUE(SELECTEDVALUE(Date_Table[Date]))

)

But output is always blank. Please help

[v-huijiey-msft · Accepted solution]

Hi @asadnsit ,

Thanks for the reply from bhanu_gautam .

As I understand it, you want to filter Emp_Master to present data based on the selected date in the slicer on Date_Table, right?

The values obtained using the slicer object in the report view cannot affect the calculated columns, calculated tables created in the data view. As a workaround, you can use measure to filter data dynamically instead of creating calculated tables.

Create a measure:

Emp_master_filtered = 
CALCULATE(
    COUNTROWS(Emp_Master),
    FILTER(
        Emp_Master,
        Emp_Master[DOJ] <= [Selected_date] &&
        Emp_Master[DOR] >= [Selected_date]
    )
)

Add Emp_master_filtered to the filter pane and set the filter rule with Emp_master_filtered = 1.

Filter the date in Slicer and the final page result is shown below:

The pbix file is attached.

If you have any other questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[asadnsit]

Thanks alot for the solution --> Using Measure as a filter resolved my issue.

[bhanu_gautam]

@asadnsit The issue you're facing is likely due to the context in which the SELECTEDVALUE function is being evaluated. When you use SELECTEDVALUE inside a calculated table, it might not be able to correctly capture the slicer context. 

Create a measure to capture the selected date:

Selected_date = MAX(Date_Table[Date])

Use this measure in your calculated table to filter Emp_Master:

Emp_master_filtered =
VAR SelectedDate = [Selected_date]
RETURN
FILTER(
Emp_Master,
Emp_Master[DOJ] <= SelectedDate &&
Emp_Master[DOR] >= SelectedDate
)