cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
gtutusaus
Frequent Visitor

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

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

1 ACCEPTED SOLUTION
v-huizhn-msft
Microsoft
Microsoft

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])


1.PNG

 

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


2.PNG

Second, create relationship between the two new tables.

3.png

Third, create a calculated column using the formula.

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

4.PNG

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")))

5.PNG

Best Regards,
Angelia

View solution in original post

7 REPLIES 7
zhangwenzhou
Regular Visitor

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] )
            )
        )
    )

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
Microsoft
Microsoft

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])


1.PNG

 

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


2.PNG

Second, create relationship between the two new tables.

3.png

Third, create a calculated column using the formula.

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

4.PNG

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")))

5.PNG

Best Regards,
Angelia

View solution in original post

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

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
Frequent Visitor

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

 

Best,

Gerónimo

Sorry, my DAX knowledge is quite limited.

Specializing in Power Query Formula Language (M)

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors