Cannot calculate DAX Measure correctly when user(s) belong to multiple projects

[naveedulislam]

I am stuck for several days and cannot find a way calculate the correct numbers. The data is organized such  that the users are assigned to certain projects in  the lookup table. And when the work in other projects the system calculates the totoal number of tests incorrectly. After spending numerous hours, I believe that I need some help as I may not be aware of the correct way to solve this problem. Any help will be highly appreciated.
The .pbix file is located at the below link:
https://1drv.ms/u/s!AqOzO2luvt21hl34vnD16lh9xxRq

Problem Description:
User u1 and u2 are assigned to the project p1 in the Users table. 
User u1 executed 15 test cases in the project p1 (this the project the user is assigned to) but the same user executed 7 test cases in project p2 as well. 
If I create a simple:

Total Tests = SUM(Tests[Tests])

then it claculates the number of tets cases executed by u1 in project p1 as 22 (p1 is selected in the project slicer). The actual number of test cases executed by this user in prokject p1 is 15.  
I managed to write another measure as:

Tests in Reality = 
CALCULATE(
    [Total Tests],
    FILTER(
        Tests,
        Tests[Projects] = RELATED(Users[Projects])
    )
)

So far so good as now I can calculate the number of tests for user u1 in project p1 correctly. 
And here comes the problem. When I select the project p2, it shows only user u7 with 25 test cases and obviously ignores the u1 since the lookup table has the user assigned to project p1.  At this point matrix should display 25 for u7 and 7 for u1 but it does not.

Adding images of data bellow as well:

This is my first post.
Thank you

[Zubair_Muhammad]

@naveedulislam

I see only one table in your attached file

Is this the correct one?

[naveedulislam]

Sorry, about that. I have replaced the file with the correct one.

Thanks

[Zubair_Muhammad · Accepted solution]

@naveedulislam

If you remove the relationship you can simply use this MEASURE

Please see attached file

Tests in Reality =
CALCULATE (
    [Total Tests],
    FILTER ( Tests, Tests[Projects] IN VALUES ( Users[Projects] ) )
)

[naveedulislam]

Finally, I resolved my issue using this solution and I had to remove project column from the existing user table and create a table which included project as one of its dynamically calulated columns. Now I always get the correct numbers since I always have the user assigned to the correct project. 
Thank you @Zubair_Muhammad for your time and help.

[naveedulislam]

@Zubair_Muhammad
Thanks so much for the reply and let me say that the solution works as described within the data model in the sample file. But the data model is a very small part of a considerable large size data model, tables, measures and relationships. Removing the relationship causes significant number of other measures and visualizations to break. 
For the last 7-8 hours I have tried this solution as well as several other solutions and hot and trail but nothing worked as desired. 
The issue is that there are some users which are not associated with certain projects but they work in those projects and result in skewed calculations.
I prepared a table for which the column headers are arranged like so:
User ID  |     Project1   |  Project2  |   Project3  |   Project4

--------- |---------------|-----------|------------|-------------

user 1    |      project1  |  project2  |  project3   | project4

and then I tried the below DAX:

Test Cases for Team = 
CALCULATE (
    [Tests Executed],
    FILTER ( 
        Executed,
        IF(Executed[ALM Project] = RELATED(Testers[Project1]), Executed[ALM Project],
            IF(Executed[ALM Project] = RELATED(Testers[Project2]), Executed[ALM Project],
                IF(Executed[ALM Project] = RELATED(Testers[Project3]), Executed[ALM Project],
                    IF(Executed[ALM Project] = RELATED(Testers[Project4]), Executed[ALM Project])
                )
            )
        )
    )
)

There was no error but all the rows were blank.

In the same way I have tried several other formulas but either I get the blank rows or incorrect numbers.

I think that the problem is that:
(1) I have to use the User ID or User Name from the Lookup Table in the tabe/matrix which 

(2) sometimes is not associated with the same project in the Test Execution table

Thank you,
Naveed

[Zubair_Muhammad]

@naveedulislam

Without breaking the relationship you can use following formula

Please see the revised file

Tests in Reality =
VAR selectedproject =
    VALUES ( Users[Projects] )
RETURN
    CALCULATE (
        [Total Tests],
        FILTER ( ALLEXCEPT ( Tests, Tests[Users] ), Tests[Projects] IN selectedproject )
    )