Manually enter or filter a value to change a column/filter

[Anonymous]

I have a list of employee IDs. I have a column of Active or Inactive that comes from the source system. I have a line chart that shows the hours they have worked over the last two years. In that line chart, I exclude the last 90 days before departure date of every Inactive employee.

I need to be able to enter an employee ID (filter, slicer, text search - I don't care as long as the end user can do it in the report) that will switch that employee from Active to Inactive in order to add them to the Inactive filter. That is, if we know someone is about to leave but they are not actually marked Inactive in the source system, I want to manually mark them as Inactive in the report.

[v-rzhou-msft]

Hi @Anonymous 

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.

Best Regards,

Rico Zhou

[v-rzhou-msft · Accepted solution]

Hi @Anonymous 

Due yo I don't know your data model, I build a sample table to have a test.

Build an ID Table with distinct ID value to build a Slicer.

ID = VALUES('Table'[EmployID])

You can change the type of ID to Text, and then you can do Search in Slicer.

Build a Measure to achieve your goal.

Dynamic Status = 
VAR _Select =
    ALLSELECTED ( 'ID'[EmployID] )
RETURN
    IF (
        ISFILTERED ( 'ID'[EmployID] ),
        IF (
            MAX ( 'Table'[Status] ) = "Active"
                && MAX ( 'Table'[EmployID] ) IN _Select,
            "Inactive",
            MAX ( 'Table'[Status] )
        ),
        MAX ( 'Table'[Status] )
    )

Result is as below.

In Default, the measure will show the same as the values in Status column.

Select one or multipy IDs in Slicer.(Ctrl+Left click) Here I select 001 and 010, they all show "Inactive".

You can download the pbix file from this link: Manually enter or filter a value to change a column/filter

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[Greg_Deckler]

@Anonymous - Perhaps you could use a What If parameter?

[Anonymous]

@Greg_Deckler I tried that, since my employee IDs are numbers. But the parameter only goes up to 99999 (5 digits) and I need 7. I don't understand why it has a cap, but unless there's some trick to get around that, it won't work.

[Greg_Deckler]

@Anonymous - OK, then just create a separate, disconnected table for your employee ids.

Employees = DISTINCT(SELECTCOLUMNS('Table',"EmployeeID",[EmployeeID]))

Something like that. Then you can use this as your "what if" parameter slicer. 

[Anonymous]

@Greg_Deckler Can you break down what to do with that table? I don't know how to set it up so an end user can enter an Employee ID and it will switch from Active to Inactive in my formula.

[Greg_Deckler]

@Anonymous - I don't have your formula or really much detail about what you are doing so it is kind of tough to be specific. But, if you have a disconnected table, you can do something like the following:

Measure =
  VAR __EmployeeID = MAX('Table'[EmployeeID])
  VAR __Table = SELECTCOLUMNS('Slicer Table',"Selections",[Column])
RETURN
  IF(__EmployeeID IN __Table,"Inactive","Active")

Again, this is just an example since I literally have no idea about what you are doing. Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.