Forum Discussion
Show rows having common elements
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
-
Add these fields from Final data :
-
Company
- CIK code
-
Apply the Measure as a Filter
-
With the table visual selected, go to the Filters pane on the right.
-
Locate your new measure ShoqInPeerList.
-
Drag ShowInPeerList into "Filters on this visual".
-
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.-
Please provvide some sample data (as a table, not picture) so helpers can upload it and build a solution.
For example save a PBIX,, excel table or csv on OneDrive or DropBox with share access, then post the link here.
You can also post data inside this chat using the grid "Table" icon.
Dont share private data.
It would alo help if you provide the corresponsing desired output.
use the same table and field names as you problem description, for clarity.
You will get a quicker and better answer, if you go to the effort of providing example data.
Thank you