topic Slice Table by Data in Multiple Columns in Power Query

Slice Table by Data in Multiple Columns

Anonymous — Wed, 13 Mar 2024 20:26:59 GMT

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.

Task	Position 1	Position 2	Position 3
1	Alex	Britney	Charles
2	Britney	Charles	Darla
3	Charles	Darla	Eric
4	Alex	Charles	Darla
5	Frank	Britney	Helen
6	Gary	Eric	Frank

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!

Re: Slice Table by Data in Multiple Columns

Anonymous — Thu, 14 Mar 2024 02:29:14 GMT

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.

Re: Slice Table by Data in Multiple Columns

Anonymous — Thu, 14 Mar 2024 16:40:27 GMT

Thank you! This worked perfectly!

Re: Slice Table by Data in Multiple Columns

Anonymous — Mon, 25 Mar 2024 21:26:31 GMT

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.