Using a single slicer on an unrelated column

[teado25]

I've created a table within Power BI that includes 2 columns, 'Allocated Worker' (column A) and 'Previous Allocated Worker' (column B). I want users to be able to choose their name from a single slicer which will filter 2 separate visuals filtered by the same slicer, 1 showing anything assigned to them (column A), and another no longer assigned to them (column B).

Is there any way to achieve this? I've experimented with measures with ISFILTERED but I can't see any way of using the created measure within the slicer.

Apologies and thanks in advance, I've been using Power BI for about 3 weeks and consider myself pretty good with Excel but Power BI is a different animal.

[Anonymous]

If you don't already have it, create a Dimension Table with the Workers Name and then create 2 relationships with the target columns, one active and one, of course, inactive. After that, create two measures. One is the measure you are already using, and it will filter the data using the active relationship. The other one, identical, needs to be wrapped inside a calculate that activates a USERELATIONSHIP, which will calculate the same measure but filtering by the second column. 

If you need some more infos on USERELATIONSHIP:
https://dax.guide/userelationship/

[teado25]

Thanks for responding so quickly. I have done the first part but would you be able to elaborate on the second part regarding the use of USERELATIONSHIP.

Thanks!

[Anonymous · Accepted solution]

In powerBI, you cannot have two active relationships between two tables. If you have a relationship active between the Workers table and the WorkerA column, the data will be filtered by that relationship by default. PowerBI will use that column to "assign" the values from the fact table to the dimension column when putting data in a visual or matrix table or when filtering with a slicer

Of course, you could also have another way of sorting the data. Like in your situation, where there is also a WorkerB column. If you need to distribute data based on this other relationship, you need some way to activate the relationship, as if it was the default one. You can do that using USERELATIONSHIP. USERELATIONSHIP is a parameter of the CALCULATE function that allows you to do that. 

The two measures could be:

DatabyWorkersA:
COUNTROWS(
                     [Table]
)

When filtering, this measure will be filtered by the default relationship (assigning values based on the WorkersA column)

DatabyWorkersB:
CALCULATE(
        COUNTROWS(
                     [Table] 
                  ),
         USERELATIONSHIP(
         WorkersTable[Worker],
         Table[WorkerB]
          )
)

When filtering, this measure will be filtered by the activated relationship (assigning values based on the WorkersB column)

I hope this is somewhat clear