Forum Discussion
Finding / Displaying Mismatch Between 2 Columns in Different Tables
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.
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
)
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
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?
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
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