Value Exists in other Table, but check 2 columns?

[Lenihan]

Hi!

I have this formula I saw in one of the other posts

It is to check if values in a column in table 1 exist in a column in table 2.

= CALCULATE( COUNTROWS(Table2), FILTER( Table2, Table2[JobNum] = EARLIER(Table1[JobNum) ) ) > 0

How can I edit this so that it checks 2 columns? and puts a match if it exists in 'either" of the two columns, but not both - just existing in one of the columns is a match

So, check if Fred in Table1[FirstName] exists in Table2[FirstName] or Table2[MiddleName]  ?   and if it exists in either, it marks True. 

I'm assuming an IF in there, unless there is another way?

[mattbrice]

While i am not 100% clear on what you are looking for, is this close?  Syntactically it should work, but not sure it is answer you are looking for...

=
CALCULATE (
 COUNTROWS ( Table2 ),
 FILTER (
 Table2,
 Table2[FirstName] = EARLIER ( Table1[Name] )
 || Table2[MIddleName] = EARLIER ( Table1[Name] )
 )
)
 > 0
 

[Lenihan]

Hmm.. the formula worked I believe, though I'm not getting the right numbers. I'm pretty sure I have a relationship issue. If the relationship between table1 and table2 is not on either of these columns of Data, will I need to use the userelationship command? or should it work anyway?

Essentially, to explain what I'm doing

This is something that would get calculated every month. I have a column called "Reporting Month" for determining which month, and I have the db relationship on a unique key.

I have 90K of records in table1 - column A is a name of a config item.

I have approx 35K of records in table2.  There are two columns that possibly could have a match to column A in the table1.

I needed the formula to determine how many of the data in Column A matched to either of the two columns in Table2. If it matches either - its TRUE. If it doesn't exist in either column, then its False. 

It would be like this

Table1

Name	Reporting Month
Jack	January
Fred	January
Amy	January
Dave	January
Tammy	January
Jack	February
Fred	February
Amy	February
Dave	February
Tammy	February

Table2

FirstName	MiddleName	Reporting month
Fred	Eugene	January
Sarah	Rob	January
Jim	Dave	January
Tammy	Mike	January
Tammy	Heather	January
Jack	John	January
Fred	John	February
Amy	Fred	February
Dave	Amy	February
Tammy	Jim	February

This is just a simplified analogy of what I'm trying. I just can't provide real data. In this sample, it is possible that Dave could appear in either First Name Column, Middle Name Column, or neither, or both. I would just need a total of how many have at least one match, by Month, as well as percentage of those that match , by month.

[mattbrice]

What fields are used for relationship between table1 and table2?  Are values in Table1 unique per month?  (Fred only listed once in January in table1?)

[Lenihan]

The active relationship is based on a unique concatenate of Name and Reporting month.. 

The values in Table1 that i'm searching for are not unique - because each month I'm added new data, and each month has the same set of values in column A.  So, For the reporting month of January - yes they are all unique, but once I add February, every name is there now twice.

It seems pretty complicated, and I'm starting to wonder if Power BI is even the best tool to use here, as at every turn I get stumped what needs to be done. 

[mattbrice]

ok - this helps.  I can look at this some more later tonight when I have time (unless someone else chimes in first).  

But to answer your concern, Power BI can most definitely do this and in my opinion it can do it better than all the other reporting tools that i have used. Having said that, Dax does have a learning curve; but once you invest the time you'll see you can solve problems like this quite efficiently.  Dax is incredibly expressive; but does take study and experience.

[danrmcallister · Accepted solution]

@Lenihan @mattbrice how about this for your evaluation column?

NameExists = if(CALCULATE(
	COUNTROWS(Table2),
	FILTER(Table2,Table2[FirstName] = EARLIER(Table1[Name] ) ||
		Table2[MiddleName] = EARLIER(Table1[Name])),
		FILTER(Table2,Table2[Reporting month] = Table1[Reporting Month])
	)
	 > 0,
	"Yes",
	"No"
)

Looks something like this - evaluates first/middle name by month without needing to do any extraeous concatenation, or anything.

[Lenihan]

I came across a problem trying to do that.. my Reporting month isn't a date type on Table 2, but it is on Table 1 so it won't let me do the compare. 

I went to change it to Date type on Table 2, and it won't let me, it says that it can't automatically convert it. 

I have another column is a merge of 2 columns - Reporting Month and another column.  As a result, it won't let me convert table2 Reporting Month to Date format - even though it let me for Table1 (and table1 has the same type of merged Column as table2).

I'm running out of hair.. so many challenges.. 

[mattbrice]

You have a way of providing a sample model?

[Lenihan]

I wish I could..  as I bet most of my problems would be solved lol

I think I'm going to tear it all down and rebuild.. start from scratch again, as I have several forum threads asking for help in various places. It is all data that is not date driven. There isn't anything to distinguish between a record in January to a record in February. I'll review the suggestions from everyone and see if I can start it correctly!

I will keep this formula and try it once i'm back to this point - I appreciate all the help. Thanks.

[Lenihan]

Hi @danrmcallister

Since starting over and building a calculated table, things have been working better.. but now stuck back on this last issue

I tried using this formula then (this is my real table names)

RelationshipExists = IF(CALCULATE(
       countrows('CI Relationship Report'),
       FILTER('CI Relationship Report', 'CI Relationship Report'[Child CI Identifier] = Earlier('All CIs'[CI Identifier]) ||
              'CI Relationship Report'[Parent] = EARLIER('All CIs'[CI Identifier])), 
               FILTER('CI Relationship Report', 'CI Relationship Report'[Reporting Month] = 'All CIs'[Reporting Month])
)
> 0,
"Yes",
"No"
)

but, it comes out as No for all values. I know there should be some matches. When I manually do it in excel its around 54% for January's data (so far I only have January and Feb raw data). 

The relationship I have to the tables is through a unique key created so that every table had that value and they all link to a calculated table. The relationship is not on the CI Identifier field (since this doesn't have unique values once I add February data)

[danrmcallister]

Hi @Lenihan, what happens to your calculated column if you deactivate the relationship between the two tables?  Your formula looks correct.

Dan

[Lenihan]

Ok, so it seems the way I was doing it prior was giving me wrong numbers.  I manually did vlookups in excel to compare, and the numbers I got matched this formula. I was able to get the percentages as well

Thank you for the help @danrmcallister and @mattbrice  !

[Lenihan]

Ok - I have Yes and No values now, but they don't appear to be right. January is showing only 22%, when it should be around 54%. 

I'm going to do some manual calcs in excel to confirm - maybe the ugly solution i was using prior was giving the wrong #s too

[Lenihan]

The previous month what I did was take all the data in table2, put it end over end on top of each other so that one column I'm checking was appended to the 2nd column of data  then I removed the duplicates, Did a count, and subtracted that from total # of records. But - I'd have to manually do that every month. I'm trying to find a better solution now that I'm appending new data every month.