Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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
Solved! Go to 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], ", ")
)
)
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"
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], ", ")
)
)
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