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

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

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
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.