Except, distinct, and filter?

interesting problem. i want to find everything in table A that’s not in a filtered version of table B, all focused on the common column “userid”.

both are multi-column tables, with different numbers of columns so we’ll need to use “distinct”.

normally i’d just do an “=except(distinct(TableA[userid], TableB[userid})”, but the twist is that I only want to include the table B userids with “resource type” equaling “Mgmt”.

I can create a new version of Table B with just the “Mgmt” resource types, but is there a way to do it all in one formula without the intermediate table?

Table A includes userids a123,c123, e123, f123, and g123.
Table B includes b123, c123, d123, and e123, but only c123 and e123 are “Mgmt”.

The “except” comparison should result in a123, c123, e123, f123, and g123.

Table B’s b123 and d123 are excluded because they don’t have resource type “Mgmt”.

Thanks for your help.Sample File.pbix (47.3 KB)

@brixmitch,

The way I would handle this is to create a table variable (vTable) in your measure equal to
FILTER(TableB, Resource Type = Management). Then the RETURN statement will be
EXCEPT(DISTINCT(TableA[userid]), vTable).

Thanks for including the PBIX file. If the above doesn’t solve your problem, I’ll be happy to generate a more specific solution from the PBIX later tonight.

Hope this is helpful.

  • Brian

@brixmitch,

See if this does the trick:

ExceptMgmt = 

VAR vTable =
FILTER(
    TableB,
    TableB[resource type] = "Mgmt"
)

VAR ADistinctID =
DISTINCT( TableA[userid] )

VAR BDistinctID =
SUMMARIZE(
    vTable,
    TableB[userid]
)

VAR ExceptAB =
EXCEPT(
    ADistinctID, 
    BDistinctID
)

RETURN
CONCATENATEX(
    ExceptAB,
    TableA[userid],
    UNICHAR(10)
) 

image

(Note: I’m confused by your initial example, since if you want Table B to include only records where resource type equals “Mgmt”, when you run the EXCEPT function, those management records will be ::subtracted" from Table A. Thus, as specified I think the result should be a123, f123 and g123.

Table A - a,c,e, f, g
Table B (mgmt only) - c,e
EXCEPT(Table A, Table B) - a,f,g

If I’ve misinterpreted, please clarify – it will be easy to revise the above DAX measure based on a change in specifications).

I hope this is helpful.

Thanks! I’ll give it a shot.

It’s great to know that you are making progress with your query @brixmitch. Please don’t forget if your question has been answered within the forum it is important to mark your thread as ‘solved’. Also, we’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!