Two columns filtered by another one presents in another table to obtain a dynamic matrix

[Anonymous]

Dear community,

I request your help on a topic to know if there is solution.

My problem is quiet simple. I have two table:

- Criticity : a couple of code give a criticity value but there is no value if there is no criticity

Code1	Code2	Criticity
X	X	2
X	Y	1
Y	Z	1
Y	X	1
Z	Y	1

-User: list of user and their code

User	Code
Toto	X
Toto	Y
Tata	Y
Tata	Z
Tata	X

I have two visual on my report:

- Matrix: Row: Code1 / Column: Code2 / Value: min of Criticity

- Slicer: User

I would like to user the slicer to filter the matrix by username but the relashionship do not allow me to filter the columns Code1 and Code2 at the same time.

I tried to duplicate User table but it gender the use of 2 slicer which is not beautiful.

Thank you in advance for your help 😉

Kind regards

[v-frfei-msft · Accepted solution]

Hi @Anonymous ,

We can achieve that without relationship. To use a measure as below and make the matrix filtered by it can work in your scenario.

Measure = var cod = VALUES(USER[Code])
return
IF(MAX(Criticity[Code1]) in cod && MAX(Criticity[Code2]) in cod,1,BLANK())

For more details, please check the pbix as attached.

[Anonymous]

Hello @v-frfei-msft, @vanessafvg 

Thank you a lot for your help, the solution with the measure is fantastic and help me a lot.

I wish you a pleasant day 😉

Kind regards,

Adhes

[vanessafvg]

how is your relationship currently set up?

can you demonstrate, give an example of wha tyou trying to do?

[Anonymous]

Hello @vanessafvg ,

At the beginning, i made a relation between "Code" from my "User" table and "Code1" from my "Criticity" table in order to obtain the criticity matrix of a specific user.

But with this relation I only obtain a filter on matrix columns.

In a second time, I duplicated "User" tables and link it "Code" table with "Code2" and add a second slicer based on this new "User" field.

With this it work, I obtain the good criticity matrix but it use 2 slicers which must have the same user selected to have a pertinent result.

Thanks in advance for your help

[vanessafvg]

If i understand correct what you need to do is create 2 relationship from the user table to the code / criticality table

you need to create 2 relationships, one from 

code = code 1

code = code 2

the first relationship will be the active relationship

the second one will be inactive and you will need to use userelationship  to do this from one table

see this video to explain

https://www.youtube.com/watch?v=sONvctPlplY&vl=en

you can then filter on the user once which is what you saying i think?