Show records of a field that appears in another field on a pivot chart

[educastri83]

Hi community, I have the following example. In a pivot chart I have a field "1" and a field "2" that contain a list of roles. I am needing to show the pairs of roles that result from combining the possible options, but with the condition that the role appears in the list of field "1". If the role of field  "2" does not exist in field "1" I am not interested in showing that line in the table.

for example in field "2" from qv file attchment, the roles: Role 1, Role 21 and Role 22 does not exist in field "1". I need to exclude those cases when the text string does not appear in dimension 1.

 It doesn't work for me to exclude it from the script since for example Role 1 I need it in the data model. The information is a dummy example but the background analysis is about segregation of duties. So it helps me to see it from a pivot chart.

Thank you very much for your help.

Eduardo

[rubenmarin]

HI, any of this 2 options can work:

- Sum({<[Field 2]={$(=Chr(39) & Concat(DISTINCT [Field 1], Chr(39) &',' & Chr(39)) & Chr(39))}>} 1)

- Sum({<[Field 2]=P([Field 1])>} 1)

[educastri83]

Thanks @rubenmarin , 

In principle for the example it worked!

I added a new field called "Risk_Level" to the example, each combination has a defined risk level ranging from 1 to 5. If I enter the risk level to the Chart pivot to see the risk level of the pair, it returns to show the roles "1", "21" and "22" with result = 0 in the Sum expression ({<[Field 2] = P ([Field 1])>} 1). How can I make it only show when the expression Sum ({<[Field 2 ([Field 1])>} 1) equals 1.? or avoid showing when the result is = 0

Attch. Examplev2 in Qv file

[rubenmarin · Accepted solution]

Hi, you can just add the expression in an If:

If(Sum({<[Field 2]=P([Field 1])>} 1)>0, sum(Risk_Level))

I also noted that selctions in [Field 2] where overwrited by set analysis so maybe you want to add a '*=':

Sum({<[Field 2]*=P([Field 1])>} 1)

[educastri83]

Works fine!

Thank you @rubenmarin