Forum Discussion

jmkvalsund's avatar
jmkvalsund
Icon for Helper III rankHelper III
3 years ago
Solved

Using measure in a DAX filter does not work

Im trying to use a measure in a DAX filter to count the numbers of customers for each Key Account Manager (KAM) at a given date, but it does not seem to work. My measure is calculated like this, a...
"CALCULATE"
"FILTER"
"MEASURE"
  • johnt75's avatar
    johnt75
    3 years ago

    Does it work if you store the result of the measure in a variable ?

    M_NumberOfCustomersTwoMonthsAgo =
VAR ReferenceDate = [M_StartDate2MonthsInterval]
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Sales'[CustomerID] ),
        'Sales'[KAM_email] = "[email protected]",
        'Sales'[DateID] = ReferenceDate
    )
johnt75's avatar
johnt75
Icon for Super User rankSuper User
3 years ago

You can't use measures as filters because they are calculated at the aggregate level not the individual row level. To calculate at the row level you need to use an iterator to introduce a row context, so you need to use the FILTER function.

Try

M_NumberOfCustomersTwoMonthsAgo =
CALCULATE (
    DISTINCTCOUNT ( 'Sales'[CustomerID] ),
    FILTER (
        'Sales',
        'Sales'[KAM_email] = "[email protected]"
            && 'Sales'[DateID] = [M_StartDate2MonthsInterval]
    )
)