Please help me to think this out: How to calculate 'reactivations' in DAX?

[gtutusaus]

Hi Community! I know I'm in the right place to get some help...

I have a table of donors that is connected to a table of pledges, and this one is also connected to a table of gifts. These donors donate monthly.

The relationships are 1 to many in the same order described. One donor can have one or more pledges (commitments of donations) and a pledge has one or more gifts (their donations really made). Pledges have two states: Active (if they are currently donating) or Inactive.

• tblDonors - PK: DONORID
• tblPledges - FK: DONORID PK: PLEDGEID
• tblGifts - FK: PLEDGEID PK: GIFTID

It happens that some donors cancel their pledges and after some time, the NGO will try to recover him or her. When they are succesfull, this is what we call a "reactivation". We want firstly to know how many were reactivated. Secondly, it would be great (desirable) to know which donor or which pledges were reactivated. 

How should we count them? Well... I need to translate in DAX the following logic and look for those

1. donors with more than one pledge (or pledges with the same DONORID)...
2. if after the date that the pledge got 'Inactive' (with LASTMODIFYDATE)
3. there is another one 'Active' pledge with its activation date (ACTIVATIONDATE) greater than LASTMODIFYDATE

Does it sound complicated? What approach would you suggest?

Best,

Gerónimo

[zhangwenzhou]

Perhaps you could add a calculated to donors with the following formula：

=

VAR table1 =
    CALCULATETABLE ( 'Pledges', 'Pledges'[PLEDGE STATUS] = "Inactivo" )
VAR table2 =
    CALCULATETABLE ( VALUES ( 'Pledges'[DATE PLEDGE ACTIVATION] ) )
RETURN
    COUNTAX (
        table1,
        COUNTROWS (
            FILTER (
                table2,
                'Pledges'[DATE PLEDGE ACTIVATION]
                    > EARLIER ( 'Pledges'[DATE PLEDGE ACTIVATION] )
            )
        )
    )

[gtutusaus]

Wow! Didn't know about variables... I added only one filter to table 1 so I skip pledges that were never activated (blank activation dates).

It seems to be working great but how do I propagate it to the pledge level? For example, I would like to know when these reactivations happened by their activation dates.

Many many thanks!
Gerónimo

[v-huizhn-msft · Accepted solution]

Hi @gtutusaus,

Based on the sample in this thread. I try to reproduce and post the following solutions.

First, my logic is filter all the donors whose status are active, and filter all the donors whose status are inactive in separate tables(Active and Inactive tables). Then get the minimum date for donors, and remove the duplicates in inactives.

 Create two table by clicking "New Tables" under modeling on home page based on the requirement above.

Active = SELECTCOLUMNS(FILTER(Pledges2,Pledges2[PLEDGE STATUS]="Activo"),"HonorId",Pledges2[DONOR ID],"Date",Pledges2[DATE PLEDGE ACTIVATION])

Inactive = DISTINCT(SELECTCOLUMNS(FILTER(Pledges2,Pledges2[PLEDGE STATUS]="Inactivo"),"HonorId",MIN(Pledges2[DONOR ID]),"Date",Pledges2[DATE PLEDGE ACTIVATION]))

Second, create relationship between the two new tables.



Third, create a calculated column using the formula.

flag = IF( ISBLANK(RELATED(Inactive[Date])),BLANK(),IF(RELATED(Inactive[Date])<Active[Date],"Yes","NO"))

Finally, create a measure to calculate reactivations, and create a card visual to display it.

reactive = COUNTROWS(DISTINCT(FILTER(Active,Active[falg]<>BLANK()&&Active[falg]="Yes")))

Best Regards,
Angelia

[gtutusaus]

Wow, Angelia! Many many thanks! Let me ask you one more thing... can I create also in Power Pivot the same kind of tables?

Best,

Gerónimo

[v-huizhn-msft]

Hi @gtutusaus,

Yeah, you can also create calculated column and measure in Power Pivot. You can post Power Pivot issue on Power Pivot forum. If my reply is helpful, please mark it as answer.

Thanks,
angelia

[gtutusaus]

@MarcelBeug, can you help me with this? It's the only post I have...

Best,

Gerónimo

[MarcelBeug]

Sorry, my DAX knowledge is quite limited.