Forum Discussion

asadnsit's avatar
asadnsit
New Member
1 year ago
Solved

Issue in using "selectedvalue" function for filtering another table

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

 

 

 

 

  • Anonymous's avatar
    Anonymous
    1 year ago

    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!

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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's avatar
      asadnsit
      New Member
      1 year ago

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

  • bhanu_gautam's avatar
    bhanu_gautam
    Super User
    1 year ago

    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
    )