Reply
jburklund
Helper III
Helper III
Partially syndicated - Outbound

Find overlap in table given a slicer value

I have a table of Investors which includes their InvestorID and which funds they have invested in ('FundName') and the year of the Fund ('FundYear'). I am trying to solve for, given a subject Fund (chosen by a slicer), how many investors overlap between each Fund in the Table and the Subject Fund. 

I tried to make a calculated table filtered by a parameter, but I have to use imported data and so I couldn't get the calculated table to filter dynamically with the parameter. I know I need to create essentially a filtered table by the Subject Fund and check if each investor record ('InvestorID') exists in the filtered table and the Input Data table so I can get my "Investors from Subject Fund" count. My report is much more complicated than this  but I've included a sample set of data below.

 

Screenshot 2023-05-02 175025.png

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

Syndicated - Outbound

@jburklund , You need to create an independent table with fund Name. Use that slicer and then you have measure below and use that with other column in visual

 

All measure in table should follow the give approach or use measure as measure level filter

 

Measure =

var _tab = summarize(filter(Table, Table[FundName] in values(FundName[FundName] )), Table[InvestorID])
return
calculate(countrows(Table), filter(Table, Table[InvestorID] in _tab))

 

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

Syndicated - Outbound

@jburklund , You need to create an independent table with fund Name. Use that slicer and then you have measure below and use that with other column in visual

 

All measure in table should follow the give approach or use measure as measure level filter

 

Measure =

var _tab = summarize(filter(Table, Table[FundName] in values(FundName[FundName] )), Table[InvestorID])
return
calculate(countrows(Table), filter(Table, Table[InvestorID] in _tab))

 

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Syndicated - Outbound

Thank you so much! This worked great and I know I will use this again.

avatar user

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

FebPBI_Carousel

Power BI Monthly Update - February 2025

Check out the February 2025 Power BI update to learn about new features.

Feb2025 NL Carousel

Fabric Community Update - February 2025

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

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)