Show rows having common elements

[AyushAwasthi]

I have a "Final Data" table with columns: Company, CIK Code and Ratio. (Every row is unique)
I have a "List Table" table with columns: Company (all values from company column in Final Data table).
Then I have a "Board data" table with columns: Individual Name, Individual Id, Organization Name and Organization Id. (Multiple individuals can be on the same company and multiple companies can have same individual)
I also have a "Match Table" table with columns: Company, CIK Code (same as Final Data table), Organization Name and Organization ID (same as Board data table). It has all the rows from Final Data table matched with Board data table.

Now I want to select a target company from a slicer on List Table, and I want to show a table visual from Final Data table with values for only those companies which have the same individuals on their board as the target company. How to achieve that?

Then I want to put the average of the ratio column of only those companies into a card visual and show the rank of the target company as compared to the peers.

Like if the company is 3rd out of the 5 companies that show up as per the criteria, the card should say "3/5". How do I achieve this?

[techies]

Hi @AyushAwasthi for peerlist, please try this measure

Shared Board Members Measure =

VAR SelectedCompanyMembers =

    CALCULATETABLE (

        VALUES('Board Data'[Individual Id]),

        TREATAS (

            VALUES('List Table'[CIK Code]),

            'Match Table'[CIK Code]

        ),

        'Board Data'

    )

VAR MatchingMembers =

    CALCULATETABLE (

        VALUES('Board Data'[Individual Name]),

        FILTER (

            'Board Data',

            'Board Data'[Individual Id] IN SelectedCompanyMembers

        )

    )

RETURN

        CONCATENATEX(MatchingMembers, [Individual Name], ", ")

 

[AyushAwasthi]

Thanks for the measure calculation, it helped in populating that column.

My only problem is the IsPeerCompany measure, the table is showing me only the target company. All the average ratio and the rank measures are working perfectly fine. But I am only seeing 1 company in the table visual.

[v-sshirivolu]

Hi  @AyushAwasthi ,
Regarding the visual showing only the target company, here are a few things to check:

Visual or Page Filters: Ensure the table visual doesn’t have a filter that restricts it to just the selected company. Double-check slicers or filters pane.

Use IsPeerCompany in Visual Filter:

Add the IsPeerCompany measure to the Filters on this visual pane.

Set it to "is TRUE".
This ensures only peer companies (as defined by your logic) appear.

Test the Measure Output:

Try putting CompanyName and IsPeerCompany into a temporary table visual.

This helps verify whether the measure is correctly returning TRUE for peers.

If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it.
Best Regards,
Sreeteja.
Community Support Team.

[AyushAwasthi]

Hi,

Visual or page filters - checked
Use IsPeerCompany in Visual Filter - done
Test the Measure Output: - Returns true only for the target company

I copied the meaure IsPeerCompany formula to a new measure and I am trying to decode which part of the formula is not giving the required result.

It fetches the correct SelectedCIK, correct SelectedBoardCode but not RelatedIndividuals. When I ran the measure till then and tried to output the result in a table, its giving me an error: MdxScript(Model) (410, 1) Calculation error in measure 'All Measures'[Testing]: A table of multiple values was supplied where a single value was expected.

Maybe this is the reason its not coming correct.

[v-sshirivolu]

Hi @AyushAwasthi !
To resolve this issue,
Use  - CONTAINS, INTERSECT, or TREATAS to compare tables, not = :

Instead of this:
IF (RelatedIndividuals = CompanyIndividuals, TRUE, FALSE  )

Replace this dax :
IF (
NOT ISEMPTY(
INTERSECT(RelatedIndividuals, CompanyIndividuals)
),
TRUE,
FALSE
)

If you’re trying to extract one value, use SELECTEDVALUE ( ) only if you're sure the result is 1 row.

To debug further:

• Temporarily output just COUNTROWS (RelatedIndividuals)in a card or table to confirm the number of rows being returned.

• That’ll show if you need to use aggregation or iterate over multiple rows.

If you’d like to share the full IsPeerCompany formula here, I can help you rewrite the logic safely to avoid scalar-table conflicts.

If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it.
Best Regards,
Sreeteja.
Community Support Team.

[AyushAwasthi]

Hi,

If I return COUNTROWS (RelatedIndividuals) it shows me the correct answer. I am using the same measure you provided i.e. ShowInPeerList.
I am not able to understand when thre ratio measure and the rank measure are working fine what is the issue for the table visual. The measure is showing 0 for all the other rows apart from thre target company. Am I missing something important here? Do I need to include any other fields?

[v-sshirivolu]

Hi @AyushAwasthi  ,

Check:

Are peer companies present in the current visual context ?

Try adding company name+  ShowInPeerList  to a table without any slicers or filters, just to confirm if peer companies ever return 1.

Inspect what the logic compares inside ShowInPeerList :

If you're doing something like:

 

INTERSECT(RelatedIndividuals, VALUES(CompanyIndividuals[PersonID]))

make sure that for each peer company row, the correct list of PersinID is available.

Add debug columns temporarily :

Add a measure like:

DebugIndividuals = CONCATENATEX(VALUES(CompanyIndividuals[PersonID]), CompanyIndividuals[PersonID], ", ")

 

This helps see if the peer companies have overlapping individuals or not.

If you're still stuck, could you share - What the ShowInPeerList logic looks like. What tables are being used (Company table, Individuals, etc.).How you’re relating individuals to companies (many-to-many?)

That would help in pinpointing where the match logic may be falling short.

If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it.
Best Regards,
Sreeteja.
Community Support Team.

[AyushAwasthi]

Hi @v-sshirivolu ,

I am using the same logic for ShowInPeerList what you shared:

ShowInPeerList =
VAR CurrentCIK = SELECTEDVALUE('Final Data'[CIK Code])
VAR SelectedCIKs = VALUES('List Table'[CIK Code])

VAR SelectedBoardCodes =
CALCULATETABLE (
VALUES('Match Table'[Board Code]),
'Match Table'[CIK Code] IN SelectedCIKs
)

VAR RelatedIndividuals =
CALCULATETABLE (
VALUES('Board Data'[Individual Id]),
'Board Data'[Organization Id] IN SelectedBoardCodes
)

VAR PeerOrgs =
CALCULATETABLE (
VALUES('Board Data'[Organization Id]),
'Board Data'[Individual Id] IN RelatedIndividuals
)

VAR PeerCIKs =
CALCULATETABLE (
VALUES('Match Table'[CIK Code]),
'Match Table'[Board Code] IN PeerOrgs
)

RETURN
IF (CurrentCIK IN PeerCIKs, 1, 0)

The measure is resulting 1 only in a single case where the CIK in the Final Data is same as the SelectedCIKs. I also put the measure CurrentCIK for each row and it is showing correctly.

I also created custom tables using the code in the measure to show if the tables are being calculated properly.
SelectedBoardCodes fetches the correct code.
RelatedIndividuals fetches the correct IDs.
PeerOrgs also is correct.
PeerCIKs also generates correct CIK codes.
Only the last part is not able to calculate correctly. Which is all the more surprising as the separate measure you created for the average ratio which uses
RETURN CALCULATE (
AVERAGE ( 'Final Data'[Ratio] ),
'Final Data'[CIK Code] IN PeerCIKs
)

in the final line works correctly. Not able to understand if this can capture the correct set of companies why can't the ShowInPeerList measure.

[v-sshirivolu]

Hi @AyushAwasthi 
Try These :

Option 1: Use CONTAINS instead of IN

ShowInPeerList =
VAR CurrentCIK = SELECTEDVALUE('Final Data'[CIK Code])
VAR SelectedCIKs = VALUES('List Table'[CIK Code])

VAR SelectedBoardCodes =
CALCULATETABLE (
VALUES('Match Table'[Board Code]),
'Match Table'[CIK Code] IN SelectedCIKs
)

VAR RelatedIndividuals =
CALCULATETABLE (
VALUES('Board Data'[Individual Id]),
'Board Data'[Organization Id] IN SelectedBoardCodes
)

VAR PeerOrgs =
CALCULATETABLE (
VALUES('Board Data'[Organization Id]),
'Board Data'[Individual Id] IN RelatedIndividuals
)

VAR PeerCIKs =
CALCULATETABLE (
VALUES('Match Table'[CIK Code]),
'Match Table'[Board Code] IN PeerOrgs
)

RETURN
IF (CONTAINS(PeerCIKs, [CIK Code], CurrentCIK), 1, 0)

Option 2: Use COUNTROWS with FILTER

RETURN
IF (COUNTROWS(FILTER(PeerCIKs, [CIK Code] = CurrentCIK)) > 0, 1, 0)

Option 3: Force context transition with CALCULATE

RETURN
CALCULATE(IF(CurrentCIK IN VALUES('Match Table'[CIK Code]), 1, 0),
'Match Table'[Board Code] IN PeerOrgs
)

The key difference is that these approaches properly handle the table filtering in the context where it's being evaluated, rather than relying on the `IN` operator which can behave differently in measure evaluation contexts.

If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it.
Best Regards,
Sreeteja.
Community Support Team.

[AyushAwasthi]

Hi,

Unfortunately none of the above ways have worked. If I don't use this measure, all the companies show up but when I put this measure in the filters on this visual field, only the slicer selected company comes up.

The rank card visual works fine and is counting 5 companies. At this point have no idea how to solve this.

[v-sshirivolu]

Hi @AyushAwasthi ,
Try any of these alternatives:

Option 1: Use CONTAINS Instead of  IN
IF (CONTAINS(PeerCIKs, 'Match Table'[CIK Code], CurrentCIK), 1, 0)

Option 2: Use  COUNTROWS with FILTER
IF (
COUNTROWS(
FILTER(PeerCIKs, 'Match Table'[CIK Code] = CurrentCIK)
) > 0,
1,
0
)

Option 3: Force Context Transition Using CALCULATE 
CALCULATE(
IF (
CurrentCIK IN VALUES('Match Table'[CIK Code]), 1, 0
),
'Match Table'[Board Code] IN PeerOrgs
)

If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it.
Best Regards,
Sreeteja.
Community Support Team.

[AyushAwasthi]

Hi,

I tried all this but the result was still the same. But I figured out what was wrong. The filter context was being carried along all the tables that we were creating in the measure.

So adding REMOVEFILTERS to all the calculated tables resolved the issue. Thank you so much for all the help.

[AyushAwasthi]

Hi,

I tried all this but the result was still the same. But I figured out what was wrong. The filter context was being carried along all the tables that we were creating in the measure.

So adding REMOVEFILTERS to all the calculated tables resolved the issue. Thank you so much for all the help.

[v-sshirivolu]

Hi @AyushAwasthi ,
If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it.
Best Regards,
Sreeteja.
Community Support Team.

[AyushAwasthi]

Hi All,

I will just add my tables here as there is no option for me to add the pbix file.

Final Data:

Company Name CIK Code Ratio

Alpha Innovations	001	1.23
Beta Solutions	002	2.45
Gamma Technologies	003	3.67
Delta Dynamics	004	4.89
Epsilon Enterprises	005	5.12
Zeta Systems	006	6.34
Eta Consulting	007	7.56
Theta Industries	008	8.78
Iota Ventures	009	9.90
Kappa Holdings	010	10.11

List Table is the just the Company and CIK Code from the Final Table.

Match Table

Company Name CIK Code Board Company Board Code

Alpha Innovations	001	Alpha Inno	22132
Beta Solutions	002	Beta S	1231
Gamma Technologies	003	Gamma Subs	214
Delta Dynamics	004	Deltex	46
Epsilon Enterprises	005	Epso Ipso	4646
Zeta Systems	006	ZeD One	346364
Eta Consulting	007	Etas	3434
Theta Industries	008	Thetos	2342
Iota Ventures	009	Impex	23423
Kappa Holdings	010	Kapri	34534

Board Data:

Individual Name Individual Id Organization Name Organization Id

John Smith	BD001	Alpha Inno	22132
John Smith	BD001	Beta S	1231
Emily Johnson	BD002	Gamma Subs	214
Emily Johnson	BD002	Deltex	46
Michael Brown	BD003	Epso Ipso	4646
Sarah Davis	BD004	ZeD One	346364
David Wilson	BD005	Etas	3434
Jessica Garcia	BD006	Thetos	2342
Daniel Martinez	BD007	Impex	23423
Laura Rodriguez	BD008	Kapri	34534
James Lee	BD009	Alpha Inno	22132
Maria Hernandez	BD010	Beta S	1231
John Smith	BD001	Gamma Subs	214
Emily Johnson	BD002	Epso Ipso	4646
James Lee	BD009	ZeD One	346364
James Lee	BD009	Gamma Subs	214

Now suppose someone selects the company Alpha Innovations in slicer from the List Table. The result table should look like:

Company	CIK Code	Board Member	Ratio
Alpha Innovations	001	All	1.23
Beta Solutions	002	John Smith	2.45
Gamma Technologies	003	John Smith James Lee	3.67
Zeta Systems	006	James Lee	6.34

 
The card visual should show the average 3.42 and another card visual should show "4/4" (4th out of 4 companies when sorted by ratio).

I know the data is complex. Thanks in advance!

[v-sshirivolu · Accepted solution]

Hi @AyushAwasthi ,

Try these steps-

Create Measures

AveragePeerRatio =
VAR SelectedCIKs =
VALUES ( 'List Table'[CIK Code] )

VAR SelectedBoardCodes =
CALCULATETABLE (
VALUES ( 'Match Table'[Board Code] ),
'Match Table'[CIK Code] IN SelectedCIKs
)

VAR RelatedIndividuals =
CALCULATETABLE (
VALUES ( 'Board Data'[Individual Id] ),
'Board Data'[Organization Id] IN SelectedBoardCodes
)

VAR PeerOrgs =
CALCULATETABLE (
VALUES ( 'Board Data'[Organization Id] ),
'Board Data'[Individual Id] IN RelatedIndividuals
)

VAR PeerCIKs =
CALCULATETABLE (
VALUES ( 'Match Table'[CIK Code] ),
'Match Table'[Board Code] IN PeerOrgs
)

RETURN
CALCULATE (
AVERAGE ( 'Final Data'[Ratio] ),
'Final Data'[CIK Code] IN PeerCIKs
)

For PeerCompanyRank :

PeerCompanyRank =
VAR SelectedCIKs =
VALUES ( 'List Table'[CIK Code] )

VAR SelectedBoardCodes =
CALCULATETABLE (
VALUES ( 'Match Table'[Board Code] ),
'Match Table'[CIK Code] IN SelectedCIKs
)

VAR RelatedIndividuals =
CALCULATETABLE (
VALUES ( 'Board Data'[Individual Id] ),
'Board Data'[Organization Id] IN SelectedBoardCodes
)

VAR PeerOrgs =
CALCULATETABLE (
VALUES ( 'Board Data'[Organization Id] ),
'Board Data'[Individual Id] IN RelatedIndividuals
)

VAR PeerCIKs =
CALCULATETABLE (
VALUES ( 'Match Table'[CIK Code] ),
'Match Table'[Board Code] IN PeerOrgs
)

VAR SelectedRatio =
CALCULATE (
MAX ( 'Final Data'[Ratio] ),
'Final Data'[CIK Code] IN SelectedCIKs
)

VAR PeerTable =
FILTER (
'Final Data',
'Final Data'[CIK Code] IN PeerCIKs
)

VAR RankedTable =
ADDCOLUMNS (
PeerTable,
"Rank", RANKX ( PeerTable, [Ratio], , DESC )
)

VAR MyRank =
MAXX (
FILTER ( RankedTable, [CIK Code] IN SelectedCIKs ),
[Rank]
)

VAR TotalPeers = COUNTROWS ( PeerTable )

RETURN
FORMAT ( MyRank, "0" ) & "/" & FORMAT ( TotalPeers, "0" )

Card Visuals

1. Card for AveragePeerRatio.

• Add a Card visual.

• Set the Value to AveragePeerRatio.

  .

2. Card for PeerCompanyRank.

• Add another Card visual.

• Set the Value to PeerCompanyRank.
  
  

 Add a Table Visual

1. Add these fields from Final data :

   • Company 

   • CIK code

Apply the Measure as a Filter

1. With the table visual selected, go to the Filters pane on the right.

2. Locate your new measure ShoqInPeerList.

3. Drag ShowInPeerList into "Filters on this visual".

4. Change the filter condition to:

   • "is"

   • "1"

Measure -  ShowInPeerList =
VAR SelectedCIKs =
VALUES ( 'List Table'[CIK Code] )

VAR SelectedBoardCodes =
CALCULATETABLE (
VALUES ( 'Match Table'[Board Code] ),
'Match Table'[CIK Code] IN SelectedCIKs
)

VAR RelatedIndividuals =
CALCULATETABLE (
VALUES ( 'Board Data'[Individual Id] ),
'Board Data'[Organization Id] IN SelectedBoardCodes
)

VAR PeerOrgs =
CALCULATETABLE (
VALUES ( 'Board Data'[Organization Id] ),
'Board Data'[Individual Id] IN RelatedIndividuals
)

VAR PeerCIKs =
CALCULATETABLE (
VALUES ( 'Match Table'[CIK Code] ),
'Match Table'[Board Code] IN PeerOrgs
)

RETURN
IF ( 'Final Data'[CIK Code] IN PeerCIKs, 1, 0 )

If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it

Find the attached pbix file for your reference.

Best Regards,
Sreeteja.
Community Support Team.

[AyushAwasthi]

The last line gives an error for the measure ShowInPeerList:

A single value for column 'CIK Code' in table 'Final Data' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

[v-sshirivolu]

Hi @AyushAwasthi ,

Replace it with this DAX :

ShowInPeerList =
VAR CurrentCIK = SELECTEDVALUE('Final Data'[CIK Code])
VAR SelectedCIKs = VALUES('List Table'[CIK Code])

VAR SelectedBoardCodes =
CALCULATETABLE (
VALUES('Match Table'[Board Code]),
'Match Table'[CIK Code] IN SelectedCIKs
)

VAR RelatedIndividuals =
CALCULATETABLE (
VALUES('Board Data'[Individual Id]),
'Board Data'[Organization Id] IN SelectedBoardCodes
)

VAR PeerOrgs =
CALCULATETABLE (
VALUES('Board Data'[Organization Id]),
'Board Data'[Individual Id] IN RelatedIndividuals
)

VAR PeerCIKs =
CALCULATETABLE (
VALUES('Match Table'[CIK Code]),
'Match Table'[Board Code] IN PeerOrgs
)

RETURN
IF (CurrentCIK IN PeerCIKs, 1, 0)


If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it

Find the above attached pbix file for your reference.

Best Regards,
Sreeteja.
Community Support Team.

[AyushAwasthi]

Tried doing that. It results only the company selected in the slicer. Rest of the measures are working fine.

[v-sshirivolu]

Hi @AyushAwasthi 
Thanks for reaching out to the Microsoft fabric community forum.


Try this steps -

Create a Measure for Selected Company :

Selected Company = SELECTEDVALUE('List Table'[Company])


Create a Measure to Identify Peer Companies :

Is Peer Company =
VAR TargetCompany = [Selected Company]
VAR TargetIndividuals =
CALCULATETABLE (
VALUES ( 'Match Table'[Individual ID] ),
'Match Table'[Company] = TargetCompany
)
VAR CurrentCompanyIndividuals =
CALCULATETABLE (
VALUES ( 'Match Table'[Individual ID] ),
'Match Table'[Company] = MAX('Final Data'[Company])
)
VAR SharedIndividuals =
INTERSECT ( TargetIndividuals, CurrentCompanyIndividuals )
RETURN
IF ( COUNTROWS(SharedIndividuals) > 0, 1, 0 )


Create Measure for Average Ratio of Peer Companies :

Avg Ratio of Peers =
CALCULATE (
AVERAGE ( 'Final Data'[Ratio] ),
FILTER (
'Final Data',
[Is Peer Company] = 1
)
)

Create Rank of Selected Company Among Peers :

Rank of Selected Company =
VAR TargetCompany = [Selected Company]
VAR PeerCompanies =
FILTER (
'Final Data',
[Is Peer Company] = 1
)
RETURN
RANKX (
PeerCompanies,
CALCULATE(SELECTEDVALUE('Final Data'[Ratio])),
,
DESC,
DENSE
)

Count Number of Peer Companies :

Peer Company Count =
CALCULATE (
DISTINCTCOUNT('Final Data'[Company]),
FILTER (
'Final Data',
[Is Peer Company] = 1
)
)

How Rank as a Text (e.g., 3/5) :

Rank Text =
VAR RankVal = [Rank of Selected Company]
VAR Total = [Peer Company Count]
RETURN
FORMAT ( RankVal, "0" ) & "/" & FORMAT ( Total, "0" )

Find attached Pbix file for your reference.

If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it

Best Regards,
Sreeteja.
Community Support Team