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

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

22 Replies

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

         

         

  • speedramps's avatar
    speedramps
    Super User
    1 year ago

    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

  • v-sshirivolu's avatar
    v-sshirivolu
    Community Support
    1 year ago

    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 

     

  • AyushAwasthi's avatar
    AyushAwasthi
    Helper I
    1 year ago

    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 Innovations0011.23
    Beta Solutions0022.45
    Gamma Technologies0033.67
    Delta Dynamics0044.89
    Epsilon Enterprises0055.12
    Zeta Systems0066.34
    Eta Consulting0077.56
    Theta Industries0088.78
    Iota Ventures0099.90
    Kappa Holdings01010.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 Innovations001Alpha Inno22132
    Beta Solutions002Beta S1231
    Gamma Technologies003Gamma Subs214
    Delta Dynamics004Deltex46
    Epsilon Enterprises005Epso Ipso4646
    Zeta Systems006ZeD One346364
    Eta Consulting007Etas3434
    Theta Industries008Thetos2342
    Iota Ventures009Impex23423
    Kappa Holdings010Kapri34534

     

    Board Data:

    Individual Name Individual Id Organization Name Organization Id

    John SmithBD001Alpha Inno22132
    John SmithBD001Beta S1231
    Emily JohnsonBD002Gamma Subs214
    Emily JohnsonBD002Deltex46
    Michael BrownBD003Epso Ipso4646
    Sarah DavisBD004ZeD One346364
    David WilsonBD005Etas3434
    Jessica GarciaBD006Thetos2342
    Daniel MartinezBD007Impex23423
    Laura RodriguezBD008Kapri34534
    James LeeBD009Alpha Inno22132
    Maria HernandezBD010Beta S1231
    John SmithBD001Gamma Subs214
    Emily JohnsonBD002Epso Ipso4646
    James LeeBD009ZeD One346364
    James LeeBD009Gamma Subs214


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

    CompanyCIK CodeBoard MemberRatio
    Alpha Innovations001All1.23
    Beta Solutions002John Smith2.45
    Gamma Technologies003John Smith
    James Lee    		3.67
    Zeta Systems006James Lee6.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's avatar
      v-sshirivolu
      Community Support
      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.

      • AyushAwasthi's avatar
        AyushAwasthi
        Helper I
        1 year ago

        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.