Comparing Values between 2 tables and displaying them using matching Date and ID's

[zyeet]

I am retrieving data from two different sources, the point of this exercise is to check if the data retrieved from both places match. If not, a prompt is sent to the client about discrepancies in their data.

The tables have the following format:

Table 1:

Table 2:

I want to achieve a dashboard where I can view both tables' Quantities and filter them according to the Customer ID and Date, something like this:

I have tried establishing a many to many relationship, but it only works on one value either ID or Date, but I would like to use both values to show the differences or discrepancies in data.

A link to a sample pbix file for what I am trying to achieve:

Comparison.pbix

[ryan_mayu · Accepted solution]

@zyeet 

you don't need to create relationships between two tables. 

pls try this

measure = sumx(FILTER(all('Sheet1 (2)'),'Sheet1 (2)'[Customer ID]=max(Sheet1[Customer ID])&&'Sheet1 (2)'[Customer Name]=max('Sheet1'[Cus Name])),'Sheet1 (2)'[Quantity])

Measure 2 = [measure]-sum(Sheet1[Quantity])

pls see the attahment below

[zyeet]

I tried doing this way, but it is not incorporating the date selected. The summed up measure is showing total quantity instead of quantity sold on a paticular date. 

[ryan_mayu]

@zyeet 

pls see the attachment below