topic Dax- compare values if matches the same key with filtering columns value in DAX Commands and Tips

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

deng2431 — Fri, 18 Nov 2022 03:49:23 GMT

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

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

amitchandak — Fri, 18 Nov 2022 04:24:17 GMT

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])

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

deng2431 — Fri, 18 Nov 2022 05:03:54 GMT

oh awesome amitchandak - love your work!!

the formula worked really well.