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,

 

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.

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

Hi AyushAwasthi !
To resolve this issue,
Use  - CONTAINS, INTERSECT, or TREATAS to compare tables, not = :

Instead of this:
IF (RelatedIndividuals = CompanyIndividuals, TRUE, FALSE  )


Replace this dax :
IF (
NOT ISEMPTY(
INTERSECT(RelatedIndividuals, CompanyIndividuals)
),
TRUE,
FALSE
)

If you’re trying to extract one value, use SELECTEDVALUE ( ) only if you're sure the result is 1 row.

To debug further:

  • Temporarily output just COUNTROWS (RelatedIndividuals)in a card or table to confirm the number of rows being returned.

  • That’ll show if you need to use aggregation or iterate over multiple rows.

If you’d like to share the full IsPeerCompany formula here, I can help you rewrite the logic safely to avoid scalar-table conflicts.

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,

     

    If I return COUNTROWS (RelatedIndividuals) it shows me the correct answer. I am using the same measure you provided i.e. ShowInPeerList.
    I am not able to understand when thre ratio measure and the rank measure are working fine what is the issue for the table visual. The measure is showing 0 for all the other rows apart from thre target company. Am I missing something important here? Do I need to include any other fields?

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

      Hi AyushAwasthi  ,

      Check:

      Are peer companies present in the current visual context ?

      Try adding company name+  ShowInPeerList  to a table without any slicers or filters, just to confirm if peer companies ever return 1.

      Inspect what the logic compares inside ShowInPeerList :

      If you're doing something like:

       
      INTERSECT(RelatedIndividuals, VALUES(CompanyIndividuals[PersonID]))

      make sure that for each peer company row, the correct list of PersinID is available.

       

      Add debug columns temporarily :

      Add a measure like:

      DebugIndividuals = CONCATENATEX(VALUES(CompanyIndividuals[PersonID]), CompanyIndividuals[PersonID], ", ")
       

      This helps see if the peer companies have overlapping individuals or not.

      If you're still stuck, could you share - What the ShowInPeerList logic looks like. What tables are being used (Company table, Individuals, etc.).How you’re relating individuals to companies (many-to-many?)

      That would help in pinpointing where the match logic may be falling short.

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