Finding / Displaying Mismatch Between 2 Columns in Different Tables

[Anonymous]

Hello, was looking to recieve some advice for this scenario. 

I'm looking at 2 text columns from 2 different tables and need to compare them and print the ones that do not match.  It needs to display the differences from the 2 filtered visuals. Visual 1 uses a column in Table 1 and Visual 2 uses a column in table 2. Both tables are recording the similar information but is recorded in 2 different systems, so the objective is finding the deltas between the 2 systems and displaying it. 

Visual 1	Visual 2		Visual 3 (Deltas)
NAME	NAME		NAME
A	A		D
B	B		F
C	C
ABC	D
	ABC
	F

Thanks in advance. 

[Zubair_Muhammad]

@Anonymous

Try the EXCEPT function in DAX

https://docs.microsoft.com/en-us/dax/except-function-dax

[Anonymous]

@Zubair_Muhammad

Thank you for the response, but this will not work for me. The 2 tables do not have the same amount of columns. The objective is, in Visual A it is showing a filtered data set from Table 1, while in Visual B it is also using the same filters, but using a data set from Table 2.  And from those 2 filtered data sets, i need to display the fields that mismatch.

I believe I need to use a measure to properly do this, but where i get stuck is that these are text fields, therefore i cannot aggregate them to be used in a measure. Using the MAX of each text field in order to be able to use a measure will not work either since it is only taking the field with the largest length. 

[v-lili6-msft]

hi, @Anonymous

Based on my test, you may try to use NOT and IN Function to add a measure as below:

Step1:

Add a name fact table

(You could use this formula add a new name table 

Name = DISTINCT(UNION(VALUES(Table1[Name]),VALUES(Table2[Name] )))

)

Step2:

Create a measure like this:

Measure = var _table=CALCULATETABLE(VALUES('Table1'[Name])) return
CALCULATE(MAX('Name'[Name]),FILTER('Name',NOT('Name'[Name]) in _table))

Step3:

Drag the name field from name table

and then drag the measure into visual level filter and set filter is not blank

Best Regards,

Lin

[Anonymous]

@v-lili6-msft Just tried this and the result sets given isn't as expected. I'm recieving a result set of the deltas, but it's not being filtered properly in terms of the current slicers/filters applied. Also, when using MAX does that just look at the text string with the longest character in the result set? 

[v-lili6-msft]

hi, @Anonymous

Use MAX just to create a measure to add it into visual level filter, and you should use name fact table as the result.

Please share your sample pbix file and expected output, and I will provide a solution for your specific report. You can upload it to OneDrive and post the link here. Do mask sensitive data before uploading.

Best Regards,

Lin

[Anonymous]

@v-lili6-msft i cannot share the PBIX file due to confidential data, and am unsure how to mask this largetset of data. I am esentially comparing Oil Rig Names between 2 tables. What i can say to paint a better picture is that visual 1 is a filtered result from a drill through, and visual 2 is a filtered result as well.  These are filtered results from 2 different tables. 

With that said, the problem I'm running into using your provided solution is that after creating the mapping table it is holding all the distinct rig names from table 1 and 2. When using the measure you provided it does help display the deltas between the two, but it is displaying the deltas for ALL the Oil rig names. I need the deltas to be from the 2 filtered visuals and not the entire list of rig names.  I'm hoping this picture can help