Comparing for allowable items

[uceaamh]

Good afternoon,

I am attempting to identify mismatched data in Power BI. However, I am totally new to BI and am intimidated by DAX.
I have two tables:
Table 1 is a list of business IDs, and what item types they are allowed to interact with
Table 2 is a list of business IDs, and what items they have interacted with

They look like:
Table 1
[TABLE="class: grid, width: 128"]
<tbody>[TR]
[TD="width: 64"]Business ID:[/TD]
[TD="width: 64"]Allowable Interactions:[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]Car[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]Toy[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]TV[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]Sweater[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]Car[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]Toy[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]Car[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]Oil[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]Table[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]Awning[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]Sweater[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]Key[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]Corn[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]Oil[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]Sweater[/TD]
[/TR]
</tbody>[/TABLE]

Table 2
[TABLE="class: grid, width: 128"]
<tbody>[TR]
[TD="width: 64"]Business ID:[/TD]
[TD="width: 64"]Items interacted with:[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]Car[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]Car[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]Oil[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]TV[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]Car[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]Car[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]Toy[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]Toy[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]Car[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]Oil[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]Table[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]Table[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]Awning[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]Car[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]Oil[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]Oil[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]Sweater[/TD]
[/TR]
</tbody>[/TABLE]

My end goal is to be able to filter out any rows where the interactions are valid. I only want to see the rows where a business has interacted with items they are not allowed to interact with.

Those two tables are created from 4 data sources:

List of Business IDs/Item Interactions
List of Unique Business IDs
List of Business IDs/Allowed Items
List of Unique Items

With relationships between:

Unique Items > List of Business IDs/Allowed Items & Unique Items > List of Business IDs/Item Interactions
Unique Business IDs>List of Business IDs/Item Interactions & List of Unique Business IDs>List of Business IDs/Allowed Items

Hoping there's an easy solution. Thanks for reading this far!

 

[gazpage]

The easy solution is to do an anti join in Power Query.

Get the two tables ready, chose Merge Queries as New, select the two table and the two columns in each to merge on (Ctrl Click), Join Kind choose left anti.

 

[uceaamh]

After watching a few videos on DAX and some reading, I was able to develop a solution (I believe).
Posted here for anyone else who may need it:
New Measure:

Allowable interaction = sumx( CROSSJOIN('Table1','Table2'),('Table2'[Item ID]='Table 1'[Item ID])*('Table 2'[Business ID]='Table 1'[Business ID])*1)

 

[theBardd]

I agree qith gazpahge, Power Query is the way to go. It is a data clean-up, so PQ is the solution. Just load the two tables into PQ, add a custom column concatenating the id and interaction in each, do a merge query with an anti-join right (assuming actual interactions are right), and it is all done.