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. 

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
6 years ago

Anonymous - Perhaps you could use a What If parameter?

  • 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.