Help! Measure won't count output from another measure correctly!

[sharpjimbo]

Hi all,

I've been staring at this problem for a couple of days now, I'd greatly appreciate some help! Link to the .pbix file attached with dummy data.

My problem is, I cannot get the [Treaty Trip Count] measure to correctly count the number of trips where [TEST OVERALL] = "Pass". The output of this measure needs to be displayed in a card, using the dummy data, it should be displaying 4 trips, but it's currently displaying 7.

[Treaty Trip Count] is currently calculated with the following DAX:

Treaty Trip Count = 
COUNTROWS(
    FILTER(
        VALUES(
            'Travel Data'[Record ID]
        ),
        [TEST OVERALL] = "Pass"
    )
)

Allow me to explain the underlying data and what the rest of the measures are trying to achieve etc.

1. Each row of the data imported represents 1 day of travel, from a home location, to a destination location. You've got record id, a name, a unique identifier, home country, desination country, the specific date that the travel occured on, wether the desination country is in the EU and finally, whether there is a social security agreement between the two countries.
2. Each record id represents a disctinct trip from home to destination. As you'll see, there are only 7 trips in this dummy data.
3. I'm firstly caclulating [Total Days], [Total Tips] and [% of Workdays in Year].
4. [Test 1 (Percent)] tests whether the total days in each trip is greater than 4% of total workdays in a year (assuming standard 223 workdays).
5. [Test 2 (Tips)] tests whether the total number of trips from that home to destination country is greater than 1.
6. [TEST OVERALL] tests that both test 1 and test 2 are a "Pass"; if they are assign "Pass".
7. [Treaty Trip Count] is supposed to be evaluating [Total Trips] where [TEST OVERALL] = "Pass".

   The issue is, all works final in the table (presumably because there's filter context in the table), but as soon as I pull it into a card, it's incorrect. Interestingly, as soon as I pull [Destination Country] out of the table, [Treaty Trip Count] is populated with 7 1s, which is presumably where this count of 7 is coming from (which is also the same result that I get in the column totals).

   I'm still very new to DAX and Power Bi, I've tried modifying the filter context of the CALCULATE functions in every way you can think of, but I'm still coming up short. I'm guessing I'm missing some fundamental understanding of how measures evaluate?

   I'm not sure if I'm asking the right question, but I'm guessing I somehow need to tell the measure what the table is it needs to evaluate without being in the context of a table visual?

   Any help/explanations would be greatly appreciated. I'll be throwing my work laptop out on to the street if I have to look at this problem for much longer!

   Thanks,

   James.