Forum Discussion
Slice data dynamically across different fields to filter a selected person
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 |
2 Replies
Hi Anonymous ,
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,
