I have two tables and I want to find content which do not match

[Anton-G]

Hello

I have two tables and I want to find content which do not match.
In the example below I want to find which Work orders that have the wrong owner.

As an example:
Work 6 is with equipment 6 and it says it's for owners 10, it should be owners 20.

Want to create a DAX question with displayed work number which has a incorrect owner.
I am working with a database that I do not have full access to, I can not create a new table.

Work_order

Work	Equipment	Owner-ID
Work 1	1	10
Work 2	2	10
Work 3	4	20
Work 4	5	20
Work 5	1	10
Work 6	6	10
Work 7	2	10
Work 8	4	20
Work 9	3	20

Equipment_owner

Equipment	Owner-ID
1	10
2	10
3	20
4	20
5	20
6	20

[v-robertq-msft]

Hi, @Anton-G 

According to your description, I can understand clearly what you want to get, you want to find content which does not match using DAX in Power BI, right? I think you can achieve this using calculated column and visual filter, you can try my steps:

1. Create a calculated column in the table ‘Work_order’:

Flag =

var _ownerID=

CALCULATE(

    MAX('Equipment_owner'[Owner-ID]),

    FILTER(

    ALL(Equipment_owner),

    [Equipment]=EARLIER(Work_order[Equipment])))

return

IF([Owner-ID]=_ownerID,1,0)

2. Create a table chart and place it like this:

3. Apply a filter to this visual like this:

And you can get what you want.

You can download my test pbix file here

If this result is not what you want, you can post some sample data(without sensitive data) and your expected result so that we can help you in advance.

How to Get Your Question Answered Quickly 

Thank you very much!

Best Regards,

Community Support Team _Robert Qin

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

[amitchandak]

@Anton-G , Create a new column in Work_order

if( work_order[Owner-ID] <> maxx(filter(Equipment_owner, Equipment_owner[Equipment] = work_order[Equipment]),Equipment_owner[Owner-ID]),1,0)

[Anton-G]

Maybe I was a little vague when I wrote "working with a database that I do not have full access to, I can not create a new table".
I'm connected to a SQL Server Analysis Services databases.
Can't add a new column.

[v-robertq-msft · Accepted solution]

Hi, @Anton-G 

According to your Follow-up reply, you said you are using live connection mode and you can’t create a new column, then I think you can try to create a measure, which can achieve the same output, you can try this measure:

Flag1 =

var _ownerID=

CALCULATE(

    MAX('Equipment_owner'[Owner-ID]),

    FILTER(

    ALL(Equipment_owner),

    [Equipment]=MAX(Work_order[Equipment])))

return

IF(MAX([Owner-ID])=_ownerID,1,0)

Then clear the filter of this table visual and set the filter like this:

And you can get what you want.

You can download my test pbix file here

Thank you very much!

Best Regards,

Community Support Team _Robert Qin

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