cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

Helper III

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

1 ACCEPTED SOLUTION
Resolver II

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

14 REPLIES 14
Solution Sage

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 `

Helper III

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.

Solution Sage

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

Helper III

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.

Solution Sage

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.

Resolver II

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

Helper III

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

Solution Sage

You have a way of providing a sample model?

Helper III

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.

Helper III

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)

Resolver II

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

Dan

Helper III

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  !

Helper III

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

Helper III

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.

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors