Compare multiple rows in one table to one row in another

[Anonymous]

Hi,

I have two example tables here. 

Table1 has a list of people (1 and 2) and their magical numbers.

Table2 has a list of people (1 and 2) and the numbers they should ignore.

How do I create a measure that gives me the sum of numbers with the exception (not minus) of the number to ignore? 

Preferably using less than and greater than in if statement. 

Expected outcome: 
1: 10

2: 12

[parry2k]

@Anonymous solution attached, it is one way to do this.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

[Anonymous]

Thank you parry2k, but I don't know if it will solve my issue. I specified the use of if and greater than/less than so that I can apply it to my real report. I believe those functions are a must, but if they are not for the example below, that would also work. 

Here is an example from the real case, and here is a link to a test .pbix file

In my report, if the hours on the left table's date are on or between the StartDate/EndDate, they should not be included in the sum. 

[v-xuding-msft · Accepted solution]

Hi @Anonymous ,

Please try like this:

Measure = 
CALCULATE(
    SUM(Table1[Hours]),
    FILTER(
        Table1,
        Table1[Date] <MAX(Table2[StartDate]) || Table1[Date] > MAX(Table2[EndDate]) ))