Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Slice Table by Data in Multiple Columns

Hi everyone.  I was looking through similar posts but could not find a question seemingly the same.

 

I have a source table with rows defined for each "task" and then 3 columns indicating the person assigned to each position of the task (see example table).  As one person may be assigned to a different position, given the task, I am looking for a way to create a slicer by "person" that would allow to filter for all tasks assigned to that person, regardless of position.

TaskPosition 1Position 2Position 3
1AlexBritneyCharles
2BritneyCharlesDarla
3CharlesDarlaEric
4AlexCharlesDarla
5FrankBritneyHelen
6GaryEricFrank

 

I HAVE had success "unpivoting" the position columns in the power queary, essentailly making 6 rows into 18, and then rebuilding the "Position" columns with DAX

 

 

Then, I rebuilt the table visual using the "last" values for each position:

 

Finally, I added a slicer for the created "Values" column (each person), and it WORKS!!!... however, the slicer then also removes the other assigned people to that task (which is definitely as expected).

 

 

My end goal is to create that table/slicer from the original data, but when a person is selected, I need to be able to see the other people assigned to that task.  I hope this makes sense!

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Anonymous ,

    Please try this way.
    I add a new table to create the slicer:

    There is no relationship between two tables:


    Use this DAX to create a measure:

    Measure = 
VAR _P = ALLSELECTED('Slicer'[Name])
RETURN
IF(
    ISFILTERED('Slicer'[Name]),
    IF(
        COUNTROWS(
            FILTER(
                _P,
                [Name] IN VALUES('Table'[Position 1]) ||
                [Name] IN VALUES('Table'[Position 2]) ||
                [Name] IN VALUES('Table'[Position 3])
            )
        ) > 0,
        1,
        0
    ),
    1
)

    Set the settings according to the following screenshot:

    The final output is as below:

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

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous ,

    Please try this way.
    I add a new table to create the slicer:

    There is no relationship between two tables:


    Use this DAX to create a measure:

    Measure = 
VAR _P = ALLSELECTED('Slicer'[Name])
RETURN
IF(
    ISFILTERED('Slicer'[Name]),
    IF(
        COUNTROWS(
            FILTER(
                _P,
                [Name] IN VALUES('Table'[Position 1]) ||
                [Name] IN VALUES('Table'[Position 2]) ||
                [Name] IN VALUES('Table'[Position 3])
            )
        ) > 0,
        1,
        0
    ),
    1
)

    Set the settings according to the following screenshot:

    The final output is as below:

    Best Regards,
    Dino Tao
    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
    2 years ago

    Thank you!  This worked perfectly!

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hello!  Me again!

     

    So, this fix has helped in getting the table to do as desired (slice by a name, and all relevant rows remain).  

     

    However, I need help knowing if I can carry this "slice/filter" across all other visuals.  For example with the sample data provided, if I wanted to add a card for counting total tasks, and then tasks assigned.  (ie. would display "6" when unfiltered, but when Britney is selected, the card would display "3").

     

    I tried applying the same method of including that measure as a filter on the "card" but it does not let me change the settings to "is 1".

     

    I hope this makes sense.  Thank you.