Forum Discussion
How to compare values in same column?
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
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
4 Replies
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" )
