Calculating an INDEX via a measure

[Anonymous]

I need to calculate an Index score for each individual in our dataset based on the number of historical transactions. I have been able to calculate this score in a calculated column, but I need need to do the calculation in a measure so we can also calculate the index for dates in the past (rolling). For now, I only focus on calculating today's values, because I want to get that working first.

What do I need to convert this calculation into a measure, so it calculates the value for each individual?

---

Engagement Index = 

VAR NumberTransactionsLastYear = 
    CALCULATE(
        COUNTROWS('Transaction by Destination'),
        FILTER('Transaction by Destination', Individuals[contactid] = 'Transaction by Destination'[_od_individual_value] &&
                                             RELATED(Transactions[Transaction Date]) >= Date(YEAR(TODAY())-1, MONTH(TODAY()), DAY(TODAY())) &&
                                             RELATED(Transactions[Transaction Date]) < TODAY()
        )
    )

VAR NumberTransactionsTwoYearsBack = 
    CALCULATE(
        COUNTROWS('Transaction by Destination'),
        FILTER('Transaction by Destination', Individuals[contactid] = 'Transaction by Destination'[_od_individual_value] &&
                                             RELATED(Transactions[Transaction Date]) >= DATE(YEAR(TODAY()), MONTH(TODAY())-24, DAY(TODAY())) &&
                                             RELATED(Transactions[Transaction Date]) < DATE(YEAR(TODAY()), MONTH(TODAY())-12, DAY(TODAY()))
        )
    )

VAR NumberTransactionsThreeYearsBack = 
    CALCULATE(
        COUNTROWS('Transaction by Destination'),
        FILTER('Transaction by Destination', Individuals[contactid] = 'Transaction by Destination'[_od_individual_value] &&
                                             RELATED(Transactions[Transaction Date]) >= DATE(YEAR(TODAY()), MONTH(TODAY())-36, DAY(TODAY())) &&
                                             RELATED(Transactions[Transaction Date]) < DATE(YEAR(TODAY()), MONTH(TODAY())-24, DAY(TODAY()))
        )
    )

VAR NumberTransactionsFourYearsBack = 
    CALCULATE(
        COUNTROWS('Transaction by Destination'),
        FILTER('Transaction by Destination', Individuals[contactid] = 'Transaction by Destination'[_od_individual_value] &&
                                             RELATED(Transactions[Transaction Date]) >= DATE(YEAR(TODAY()), MONTH(TODAY())-48, DAY(TODAY())) &&
                                             RELATED(Transactions[Transaction Date]) < DATE(YEAR(TODAY()), MONTH(TODAY())-36, DAY(TODAY()))
        )
    )

VAR NumberTransactionsHistory = 
    CALCULATE(
        COUNTROWS('Transaction by Destination'),
        FILTER('Transaction by Destination', Individuals[contactid] = 'Transaction by Destination'[_od_individual_value] &&
                                             RELATED(Transactions[Transaction Date]) < DATE(YEAR(TODAY()), MONTH(TODAY())-48, DAY(TODAY()))
        )
    )

VAR DEIID = 
    (IF(NumberTransactionsLastYear <> 0, 16, 0)) +
    (IF(NumberTransactionsTwoYearsBack <> 0, 8, 0)) +
    (IF(NumberTransactionsThreeYearsBack <> 0, 4, 0)) +
    (IF(NumberTransactionsFourYearsBack <> 0, 2, 0)) + 
    (IF(NumberTransactionsHistory <> 0, 1, 0)) 

VAR INDEX =
    SWITCH(DEIID,
    0,0,
    1,-8,
    2,-7,
    3,-7,
    4,-6,
    5,-6,
    6,-5,
    7,-5,
    8,-4,
    9,-4,
    10,-3,
    11,-3,
    12,-2,
    13,-2,
    14,-1,
    15,-1,
    16,1,
    17,2,
    18,3,
    19,3,
    20,4,
    21,4,
    22,5,
    23,5,
    24,6,
    25,6,
    26,7,
    27,7,
    28,8,
    29,8,
    30,9,
    31,9
)

RETURN
    INDEX

---

[v-yanjiang-msft]

Hi @Anonymous ,

Is your problem solved?? If so, Would you mind accept the helpful replies as solutions? Then we are able to close the thread. More people who have the same requirement will find the solution quickly and benefit here. Thank you.

Best Regards,
Community Support Team _ kalyj

[AlexisOlson]

Since you don't have row context, you need to use an aggregator like VALUES or MAX to grab the Individuals[contactid].

I'd try something like this:

Engagement Index =
VAR _Today = TODAY ()
VAR _1 = DATE ( YEAR ( _Today ) - 1, MONTH ( _Today ), DAY ( _Today ) )
VAR _2 = DATE ( YEAR ( _Today ) - 2, MONTH ( _Today ), DAY ( _Today ) )
VAR _3 = DATE ( YEAR ( _Today ) - 3, MONTH ( _Today ), DAY ( _Today ) )
VAR _4 = DATE ( YEAR ( _Today ) - 4, MONTH ( _Today ), DAY ( _Today ) )
VAR _Individuals =
    TREATAS (
        VALUES ( Individuals[contactid] ),
        'Transaction by Destination'[_od_individual_value]
    )
VAR NumberTransactionsLastYear =
    CALCULATE (
        COUNTROWS ( 'Transaction by Destination' ),
        KEEPFILTERS ( _Individuals ),
        Transactions[Transaction Date] >= _1,
        Transactions[Transaction Date] < _Today
    )
VAR NumberTransactionsTwoYearsBack =
    CALCULATE (
        COUNTROWS ( 'Transaction by Destination' ),
        KEEPFILTERS ( _Individuals ),
        Transactions[Transaction Date] >= _2,
        Transactions[Transaction Date] < _1
    )
VAR NumberTransactionsThreeYearsBack =
    CALCULATE (
        COUNTROWS ( 'Transaction by Destination' ),
        KEEPFILTERS ( _Individuals ),
        Transactions[Transaction Date] >= _3,
        Transactions[Transaction Date] < _2
    )
VAR NumberTransactionsFourYearsBack =
    CALCULATE (
        COUNTROWS ( 'Transaction by Destination' ),
        KEEPFILTERS ( _Individuals ),
        Transactions[Transaction Date] >= _4,
        Transactions[Transaction Date] < _3
    )
VAR NumberTransactionsHistory =
    CALCULATE (
        COUNTROWS ( 'Transaction by Destination' ),
        KEEPFILTERS ( _Individuals ),
        Transactions[Transaction Date] < _4
    )
VAR DEIID =
    IF ( NumberTransactionsLastYear             > 0, 16, 0 )
        + IF ( NumberTransactionsTwoYearsBack   > 0, 8,  0 )
        + IF ( NumberTransactionsThreeYearsBack > 0, 4,  0 )
        + IF ( NumberTransactionsFourYearsBack  > 0, 2,  0 )
        + IF ( NumberTransactionsHistory        > 0, 1,  0 )
VAR INDEX =
    SWITCH (
        DEIID,
        0,  0,
        1,  -8,
        2,  -7,
        3,  -7,
        4,  -6,
        5,  -6,
        6,  -5,
        7,  -5,
        8,  -4,
        9,  -4,
        10, -3,
        11, -3,
        12, -2,
        13, -2,
        14, -1,
        15, -1,
        16,  1,
        17,  2,
        18,  3,
        19,  3,
        20,  4,
        21,  4,
        22,  5,
        23,  5,
        24,  6,
        25,  6,
        26,  7,
        27,  7,
        28,  8,
        29,  8,
        30,  9,
        31,  9
    )
RETURN
    INDEX

[Anonymous]

Thanks for your help and for cleaning the code 🙂 Unfortunately, this is not resulting in what I am looking for. I would like to be able to group the number of individuals based on their index number. This works when I use a calculated column (left table), but not when I use the measure. However, when I use the measure in the table together with contactid, it shows the right index per contactid. So the calculation works for individual rows, but I am not able to do groupings, etc.

[AlexisOlson · Accepted solution]

Ah, I see. Yes, this is much easier with a calculated column.

To do this with measures, you'll need to create a new dimension table for the index to use on the matrix rows and then write a measure that counts the number of individuals that fall into each index bucket.

Something like this for example:

Number of Individuals =
VAR _Indices_ = VALUES ( dimIndex[Index] )
VAR _Individuals_ =
    ADDCOLUMNS (
        VALUES ( Individuals[contactid] ),
        "@Engagement", [Engagement Index]
    )
VAR _Filtered_ = FILTER ( _Individuals_, [@Engagement] IN _Indices_ )
RETURN
    COUNTROWS ( _Filtered_ )

Here are some other related patterns:

https://www.daxpatterns.com/dynamic-segmentation/