cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
gauravnarchal
Post Prodigy
Post Prodigy

Measure to compare values in two different table

Hello, I need help to check if the numbers in table 1 are available in table 2 and find out the ones that do not exist in table 2. True / False.

 

I need this as measure and don't want to create a calculated column,

 

Table 1

 

Number
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

 

Table 2

 

Number
1
3
7
8
10
12
13
15

 

Result

 

NumberResult
1TRUE
2FALSE
3TRUE
4FALSE
5FALSE
6FALSE
7TRUE
8TRUE
9FALSE
10TRUE
11FALSE
12TRUE
13TRUE
14FALSE
15TRUE
1 ACCEPTED SOLUTION
v-yalanwu-msft
Community Support
Community Support

Hi,  @gauravnarchal ;

Try it.

Result = IF(MAX('Table1'[Number]) in VALUES(Table2[Number]),"TRUE","FALSE")

The final output is shown below:

vyalanwumsft_0-1643855368920.png
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
bigchippah
Helper I
Helper I

@v-yalanwu-msft, @MargaritaG, @Tahreem24, @amitchandak 

Not to piggyback, but I have an additional question to the above solutions.

 

Table 1 is an employee roster. (Employee ID, name, work location)

Table 2 is a list of employees who have been audited (Employee ID, name, date audited)

 

The measures above show me a True/False if an employee appears on Table 2 (the audit list).  However, I would like to use a slicer to filter by work location.  Currently, my slicer returns the entire list of employees (with the correct True/False result ) instead of only those who are assigned to the sliced/selected work location.

 

Any ideas?

v-yalanwu-msft
Community Support
Community Support

Hi,  @gauravnarchal ;

Try it.

Result = IF(MAX('Table1'[Number]) in VALUES(Table2[Number]),"TRUE","FALSE")

The final output is shown below:

vyalanwumsft_0-1643855368920.png
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

MargaritaG
Resolver I
Resolver I

Hey, 
You could write measure like this to get the solution required.

MargaritaG_0-1643365116499.png

Let me know if this works!

Tahreem24
Super User
Super User

@gauravnarchal Join the both table using relationship. Then create a measure in table 1:

Match = IF(SUM(Table1[Number]) IN {SUM(Table2[Number])}, TRUE(),FALSE())
Capture.PNG
 
Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, Sales Analysis Report , and Fortune 500 Companies Analysis
amitchandak
Super User
Super User

@gauravnarchal , A measure to me used with table 1 Number

Measure = if(isblank(COUNTX(filter(Table1, Table1[Number] in values(Table2[Number])),Table1[Number])), FALSE(),TRUE())

Helpful resources

Announcements
Join Arun Ulag at MPPC23

Join Arun Ulag at MPPC23

Get a sneak peek into this year's Power Platform Conference Keynote.

PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Top Solution Authors