Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Anonymous
Not applicable

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

Hi,

 

I have a dataset of the following structure:

 

Benchmark IDSecurity ID
ID1SID1
ID1SID2
ID2SID3
ID2SID4
ID2SID1

 

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 IDSecurity ID
ID1SID2
ID2SID3
ID2SID4

 

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

1 ACCEPTED SOLUTION
johnt75
Super User
Super User

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

johnt75_0-1648225170706.png

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], ", ")
    )
)

View solution in original post

3 REPLIES 3
tamerj1
Super User
Super User

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"
1.png

johnt75
Super User
Super User

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

johnt75_0-1648225170706.png

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
Not applicable

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

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!