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.-
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.
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.
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.
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.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.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.
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.