How to compare values in same column?

[Ilka719]

How could I compare is there are same value in different groups in same table?
Trying this code:

Same =

var KP = CALCULATE(VALUES(Table[Key]),FILTER(Table,Table[Group]="Apple"))

var KA = CALCULATE(VALUES(Table[Key]),FILTER(Table,Table[Group]="Orange"))

return

if(KP=KA,"same","no")

But detects error: A circular dependency was detected

[danextian]

Hi @Ilka719 ,

If you're trying to determine if a particualr key in  apple exists in orange as well, try this:

=
IF (
    (
        CALCULATE (
            DISTINCTCOUNT ( 'table'[key] ),
            FILTER (
                'table',
                'table'[key] = EARLIER ( 'table'[key] )
                    && 'table'[group] = "Apple"
            )
        )
            + CALCULATE (
                DISTINCTCOUNT ( 'table'[key] ),
                FILTER (
                    'table',
                    'table'[key] = EARLIER ( 'table'[key] )
                        && 'table'[group] = "Orange"
                )
            )
    ) > 1,
    "same",
    "no"
)

[Ilka719]

Hi! 🙂
Tried, but the result is endless: 

😞

[danextian]

@Ilka719 

I'm assuming you have a very large table. One more fact that should be mentioned when asking for help. Or  possibly, there are other calculations that are slowing this down.

[danextian · Accepted solution]

I would create a summary table that contains the group to lessen the number of rows to be scanned  and key then create a calculatead column to count whether the key has more than one distinct group. The summary table can either be created in DAX  or M. Please see attached sample.

Summary calc table

SummaryTable =
SUMMARIZE ( 'DataTable', 'DataTable'[Group], 'DataTable'[Key] )

calc column in SummaryTable

same_no = 
IF (
    CALCULATE (
        DISTINCTCOUNT ( SummaryTable[Group] ),
        ALLEXCEPT ( SummaryTable, SummaryTable[Key] )
    ) > 1,
    "same",
    "no"
)

You can then use this table to relate to the original table or as a reference in LOOKUPVALUE. Please see attached sample pbix. https://1drv.ms/f/s!AqxKG9R1145fiHWd4q_vHpM9NRlt?e=669nxs