simple filter measure

[dw700d]

I have a list of over 5,000 Purchase order numbers. Each Purchase order can have a variety of different account codes attached to it. There are 14 account codes. I would like to Identify Purchase orders that do not have account code (10C) attached to it. For Example in the table below  Purchase order 2,4,6,8 would qualify as a purchase order that does not have account code 10C. Purchase order 1 would not qualify because although it has other account codes it also has 10C. How can I create a measure that allows me to only view purchase orders without account code 10C?

Purchase Order No	Acct Cd	Sum of Amt
1	10C	9624.26
1	20B	14209.52
1	20C	9001.25
2	20D	3624.93
2	20B	26953.98
3	10C	1750.5
4	20E	1425.67
4	20D	35491.18
4	20A	2450.15
5	10C	12901.04
6	20A	4593.94
6	20B	833.9
7	10C	200
7	20A	486.39
7	20B	100
8	20A	156
8	20C	122

[VahidDM · Accepted solution]

Hi @dw700d 

Try this measure :

Purchase <>(10C) = 
VAR _Count10C =
    CALCULATE (
        COUNT ( 'Table'[Sum of Amt] ),
        ALLEXCEPT ( 'Table', 'Table'[Purchase Order No] ),
        'Table'[Acct Cd] = "10C"
    )
RETURN
    IF ( ISBLANK ( _Count10C ), 0, _Count10C )

and filter the new meausre to show zero.

Output:

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos✌️!!

[amitchandak]

@dw700d , Plot this as total or with purcahse order no

Measure =
var _1 = calculate(sum(Table[Amt]), filter(Table, Table[Acc Cd] ="10C"))
return
countx(values(Table[Purchase Order No]), if(isblank(_1) ,[Purchase Order No], blank()))