Reply
Member
Posts: 55
Registered: ‎01-26-2017
Accepted Solution

Value Exists in other Table, but check 2 columns?

 

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?


Accepted Solutions
Regular Visitor
Posts: 33
Registered: ‎02-15-2017

Re: Value Exists in other Table, but check 2 columns?

@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.

 

PBI Column Multiple Filters.jpg

View solution in original post


All Replies
Established Member
Posts: 148
Registered: ‎02-14-2017

Re: Value Exists in other Table, but check 2 columns?

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

 

Member
Posts: 55
Registered: ‎01-26-2017

Re: Value Exists in other Table, but check 2 columns?

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

NameReporting Month
JackJanuary
FredJanuary
AmyJanuary
DaveJanuary
TammyJanuary
JackFebruary
FredFebruary
AmyFebruary
DaveFebruary
Tammy

February

 

Table2

FirstNameMiddleNameReporting month
FredEugeneJanuary
SarahRobJanuary
JimDaveJanuary
TammyMikeJanuary
TammyHeatherJanuary
JackJohnJanuary
FredJohnFebruary
AmyFredFebruary
DaveAmyFebruary
TammyJimFebruary

 

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.

Established Member
Posts: 148
Registered: ‎02-14-2017

Re: Value Exists in other Table, but check 2 columns?

[ Edited ]

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?)

Member
Posts: 55
Registered: ‎01-26-2017

Re: Value Exists in other Table, but check 2 columns?

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. 

Member
Posts: 55
Registered: ‎01-26-2017

Re: Value Exists in other Table, but check 2 columns?

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.

Established Member
Posts: 148
Registered: ‎02-14-2017

Re: Value Exists in other Table, but check 2 columns?

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.

Regular Visitor
Posts: 33
Registered: ‎02-15-2017

Re: Value Exists in other Table, but check 2 columns?

@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.

 

PBI Column Multiple Filters.jpg

Member
Posts: 55
Registered: ‎01-26-2017

Re: Value Exists in other Table, but check 2 columns?

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.. 

Established Member
Posts: 148
Registered: ‎02-14-2017

Re: Value Exists in other Table, but check 2 columns?

You have a way of providing a sample model?