Return table of ID's for which a specific Attribute value doesn't appear

[aloosh89]

Hi,

Below is a sample table: 

ID	Attribute	Name
1	A	Alex
1	B	Alex
1	C	Alex
2	B	John
2	C	John
2	D	John
3	A	Debra
3	B	Debra
3	C	Debra
4	B	Lana
4	C	Lana
4	D	Lana

I want to create a table (using calculatetable or similar) which returns all the ID's for which the Attribute value 'A' doesn't occur. Hence for the sample above, the result would be a table produced which looks like this:

ID	Name
2	John
4	Lana

Appreciate the help.

Thanks,

Ali 

[parry2k]

@ThxAlot actually with a larger table, your DAX expression is taking longer, not sure why.

[parry2k]

@ThxAlot Good option but I reviewed the performance, not much of a difference. 🤷‍♀️

[ThxAlot]

Simple enough,

[aloosh89]

@ThxAlot I am trying to implement this. If I have an additional column in the table, let's call it 'Attribute 2' which I want to use filter some of the rows, where in the expression would I add that additional filtering? I have an example below, let's say I want to do exactly as requested in the original post, but also filter out rows by 'Attribute2 = x' only independant of what attribute1 is. So in this example the only row returned would be one row for John. 

ID	Attribute	Name	Attribute2
1	A	Alex	x
1	B	Alex	x
1	C	Alex	x
2	B	John	x
2	C	John	x
2	D	John	x
3	A	Debra	x
3	B	Debra	x
3	C	Debra	x
4	B	Lana	y
4	C	Lana	y
4	D	Lana	y

[parry2k · Accepted solution]

@aloosh89 try this:

New Table = 
VAR __ExcludeTable = CALCULATETABLE ( VALUES ( MyTable1[ID] ), MyTable1[Attribute] = "A" ) 
VAR __RestTable = EXCEPT ( VALUES ( MyTable1[ID] ), __ExcludeTable )
RETURN
SUMMARIZE ( 
        NATURALINNERJOIN ( MyTable1, __RestTable ),
        [ID],
        [Name]
    )

[aloosh89]

@parry2k this solution works perfectly, thanks so much and sorry for the delayed response.