Select two categories and highlight the unique values within each category in a table

[Anonymous]

Hi,

I have a dataset of the following structure:

Benchmark ID	Security ID
ID1	SID1
ID1	SID2
ID2	SID3
ID2	SID4
ID2	SID1

I am looking for a way for user to select two benchmark id's from a slicer and then have a visual that highlights the security id's that are unique to each. For example the output from the dataset above would be something like:

Benchmark ID	Security ID
ID1	SID2
ID2	SID3
ID2	SID4

Is what I am describing possible? The solution requires users to be able to compare any two benchmark ID's using a slicer.

Thanks,

Ben

[tamerj1]

hi @Anonymous 
Yes possible

Filter Measure = 
VAR Duplicates =
    CALCULATETABLE ( Data, ALLEXCEPT ( Data,Data[Security ID] ) )
VAR Result =
    COUNTROWS ( Duplicates )
RETURN
    Result 

Just place it in the filter pane and select "Is" and insert value "1"

[johnt75 · Accepted solution]

I don't think its possible to do exactly what you're looking for, as a measure can only return a single value, but it is possible to produce a comma separated list of the unique values, like

To do this, create 2 disconnected tables Slicer 1 and Slicer 2 and then create a measure Unique SIDs using the below

Slicer 1 = VALUES(Benchmark[Benchmark ID])

Slicer 2 = VALUES(Benchmark[Benchmark ID])

Unique SIDs = 
var slicer1Values = CALCULATETABLE( VALUES(Benchmark[Security ID]), TREATAS( VALUES('Slicer 1'[Benchmark ID]),Benchmark[Benchmark ID]))
var slicer2Values = CALCULATETABLE( VALUES(Benchmark[Security ID]), TREATAS( VALUES('Slicer 2'[Benchmark ID]),Benchmark[Benchmark ID]))
return IF( SELECTEDVALUE(Benchmark[Benchmark ID]) = SELECTEDVALUE('Slicer 1'[Benchmark ID]),
    CONCATENATEX( EXCEPT(slicer1Values, slicer2Values), [Security ID], ", "),
    IF( SELECTEDVALUE(Benchmark[Benchmark ID]) = SELECTEDVALUE('Slicer 2'[Benchmark ID]),
        CONCATENATEX( EXCEPT(slicer2Values, slicer1Values), [Security ID], ", ")
    )
)

[Anonymous]

Hi @johnt75 ,

Thank you so much for that solution! I've spent days trying to find a solution to this problem myself and I really appreciate the your help.

Have a great day.

Thanks,

Ben