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.

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.

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

        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.