Dax- compare values if matches the same key with filtering columns value

[deng2431]

Hi All,

I couldnt figure out this filtering concept... I am trying validate if any [Invoice Key] is the same when [Full Exclusion]

 is marked as opposite to each other (Yes/No). if in column [Full Exclusion] is 'Yes' then compare if any [Invoice Key] exist with the 'No' then in the [Double Exclusion] column mark that row as 'Yes' 

i tried this column measure but doesnt get the desired result:

Double Exclusion =
var _Ex3 = CALCULATE(maxa('Fact PTRS Full Detail list'[Invoice Key]),'Fact PTRS Full Detail list'[Full Exclusion] = "Yes")
var _Ex4 = CALCULATE(MAXA('Fact PTRS Full Detail list'[Invoice Key]),'Fact PTRS Full Detail list'[Full Exclusion] = "No")
Return

IF(_Ex3 = _Ex4, "Yes","No")

The issue i am trying to solve is there is 2 table on the PowerBI report, 1 is for the [Full Exclusion] = Yes and other is [Full Exclusion] = No

but I need to make a note of which invoice number exists in both tables. 

Any help is really appreciated!! Cheers  

[deng2431]

oh awesome @amitchandak - love your work!!

the formula worked really well. 

[amitchandak · Accepted solution]

@deng2431 , a new column

New columm=
var _cnt = countx(filter('Fact PTRS Full Detail list',[Invoice Key] = earlier([Invoice Key]) && [Full Exclusion] = "No"), [Invoice Key])
return
if(isblank(_cnt), "No", [Full Exclusion])