Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
33 | |
16 | |
13 | |
10 | |
8 |
User | Count |
---|---|
59 | |
20 | |
12 | |
11 | |
10 |