Forum Discussion

AyushAwasthi's avatar
AyushAwasthi
Helper I
1 year ago
Solved

Show rows having common elements

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). ...
  • v-sshirivolu's avatar
    v-sshirivolu
    1 year ago

    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.

techies's avatar
techies
Super User
1 year ago

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's avatar
    AyushAwasthi
    Helper I
    1 year ago

    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's avatar
      v-sshirivolu
      Community Support
      1 year ago

      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's avatar
        AyushAwasthi
        Helper I
        1 year ago

        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.