Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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

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...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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. 

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
6 years ago

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's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      Greg_Deckler
      Icon for Community Champion rankCommunity Champion
      6 years ago

      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.