Power BI compare values in same column based on filter from another columns

Question

I am trying to create a column to see if a keyword is shared between 2 accounts on power bi. Like the example below. Bold is the result column required

Account    Search Keyword    Shared Keyword
Account A  abc               Yes
Account B  abc               Yes
Account A  def               No
Account B  ghi               Yes
Account A  ghi               Yes
Account A  abc               Yes
Account A  abc               Yes
Account A  def               No
Account B  ghi               Yes
Account A  ghi               Yes

The keyword can occur multiple times in the same account. I tried the following code but am getting error "A circular dependency has been detected: 'Table'[Search keyword]"

Shared Keyword = 
var AccA = CALCULATE(VALUES('Table'[Search keyword]),FILTER('Table','Table'[Account (groups)]="Account A"))
var AccB = CALCULATE(VALUES('Table'[Search keyword]),FILTER('Table','Table'[Account (groups)]="Account B"))
return
if(AccA=AccB,"Yes","No")

Please help me resolve this

mkRabbani mkRabbani · Accepted Answer · 2020-08-26T07:13:51

You can use this below Measure for your purpose-

is_shared = 

VAR current_row_key = MIN(Table[Search Keyword])

VAR count_key_occurance = 
CALCULATE(
    DISTINCTCOUNT(Table[Account]),
    FILTER(
        ALL(Table),
        Table[Search Keyword] = current_row_key
    )
)

RETURN IF(count_key_occurance > 1, "Yes", "No")

Here is the final output in my test-

Power BI compare values in same column based on filter from another columns

1 Answers