Forum Discussion
Using a single slicer on an unrelated column
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
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/
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!
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