topic Re: Calculating an INDEX via a measure in DAX Commands and Tips

Calculating an INDEX via a measure

Anonymous — Thu, 07 Apr 2022 06:56:53 GMT

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

---

Re: Calculating an INDEX via a measure

AlexisOlson — Thu, 07 Apr 2022 20:47:18 GMT

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

Re: Calculating an INDEX via a measure

Anonymous — Fri, 08 Apr 2022 07:23:59 GMT

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.

Re: Calculating an INDEX via a measure

AlexisOlson — Fri, 08 Apr 2022 14:52:50 GMT

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/

Re: Calculating an INDEX via a measure

v-yanjiang-msft — Fri, 06 May 2022 06:19:09 GMT

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