Slice data dynamically across different fields to filter a selected person

[HimanshuSingh]

I have orders data by different rep, and their managerial hierarchy. I want to allow the user to select for a person, and all the rows should be filtered (whether he is the rep or a rep's manager). So in the below example B is a rep as well a manager of A. When a user selects B, they should see both Order 1 and Order 2.

Order	Rep	Manager L1
1	A	B
2	B	D
3	C	E

[DataNinja777]

Hi @HimanshuSingh ,

You can achieve this dynamic filtering in Power BI using a DAX measure or a calculated column. The goal is to allow users to select a person and filter the data so that all rows where they appear as either a Rep or Manager L1 are shown. To do this, you can create a measure that checks whether the selected person is in either of these fields.

SelectedPersonOrders = 
VAR SelectedPerson = SELECTEDVALUE('SelectionTable'[Person]) 
RETURN
IF(
    MAX('Orders'[Rep]) = SelectedPerson || MAX('Orders'[Manager L1]) = SelectedPerson, 
    1, 
    0
)

This measure can be applied as a visual filter where the value is set to 1. Users can select a person from a slicer, and the table will dynamically update to show all relevant orders. Alternatively, if you need the filtering to work in more scenarios, you can create a calculated column instead.

IsSelectedPerson = 
VAR SelectedPerson = SELECTEDVALUE('SelectionTable'[Person])
RETURN 
IF(
    'Orders'[Rep] = SelectedPerson || 'Orders'[Manager L1] = SelectedPerson, 
    "Yes", 
    "No"
)

By applying a filter where "Yes" is selected, only relevant rows will be displayed. For example, if the user selects B, the table will show both Order 1 (where B is a manager) and Order 2 (where B is the rep). This ensures that both reps and their direct managers are dynamically included.

Best regards,

[HimanshuSingh]

For the second option of filtering, how do I use selectedvalue. I am guessing the selectiontable will be a union of all the reps, managers. I added the selectiontable values to a slicer. but it did not work