Except, distinct, and filter?

DAX DAX Calculations
3

@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.