DAX code to countrows when comparing values in two columns

0
votes

How can I compare values in two related tables to do a count in a measure?

Here is the measure formula I wrote - which does not work:

PoliciesPurchasedAsStudent = CALCULATE(Countrows(Policies),People[Date Graduated]>=Policies[Date Purchased]))

The People table contains one record per customer (including a column for Date Graduated) The Policies table contains all the policies owned by all the customers (including a column for Date Purchased) The tables are linked on the Customer ID number.

I just want to know how many policies were purchased by customers before they graduated.

What am I doing wrong?

1
comparedaxmeasure

1 Answers

3
votes

Try using the RELATED() function which lets you get the related value in Policies table.

PoliciesPurchasedAsStudent =
CALCULATE (
    COUNTROWS ( Policies ),
    FILTER (
        People,
        People[Date Graduated] >= RELATED ( Policies[Date Purchased] )
    )
)

Let me know if this helps.