locked
Please help me to solve this out: How to calculate 'reactivations' in DAX? (for Excel 2016 Pro) RRS feed

  • Question

  • Hi Community!

     

    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

    I got valuable help already here from Angelia but for Power BI. I am using PowerPivot from Excel Pro 2016. I cannot create new tables so I was looking for another way to create a calculated measure.

    Here it is a minimalist sample to play with: https://drive.google.com/file/d/0B27DguM4UkOcVFVSSUwxSjVuVnc/view?usp=sharing.

    Hope you can help me. Always thankful,

    Gerónimo


    • Edited by gerotutu Wednesday, May 17, 2017 5:24 AM
    Wednesday, May 17, 2017 5:20 AM

All replies

  • Hi gerotutu,

    After I research, it is hard to achieve your requirement because your inactive and active status value in same column, and which has no regular in your resource table. In Power Pivot, you can install DAX studio, and connect it to your data model. Please see the following screenshots.




    In addition, it's nice to post your issue here. Somebody who is happy to help others will share their ideas if they had better solution.

    Thanks,
    Angelia


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, May 18, 2017 9:51 AM
  • 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] )
                )
            )
        )

    Friday, May 19, 2017 4:28 AM
  • How do I do this? I assumed that you referred to create 2 different calculated columns called 'VAR table1' and another one called 'VAR table2'... am I right? (and if this is the way... I still couldn't follow the logic on how to get final number) 

    When I use these calculatetable() formulas in a column a get always an error: "The expression refers to multiple column. Multiple columns cannot be converted to a scalar value"

    I don't know honestly how to use them and DAX Studio seems a bit pro to me yet.

    Thanks for your answers. Best,
    Gerónimo

    Monday, May 22, 2017 4:21 AM
  • No, you just need to add a calculated column to donors table with the  enentire formula above.

    Variables  are a new feature in DAX 2015  and  a major feature that makes writing DAX code easier.

    For more details, you can have a look at:

    https://www.sqlbi.com/articles/variables-in-dax/

    Monday, May 22, 2017 7:55 AM
  • Wow! Didn't know about this... 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

    Saturday, May 27, 2017 1:42 AM
  • Hi gerotutu,

    If you create a relationship between two tables, and set it as active, the reactivation happened.

    Best Regards,
    Angelia

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, June 1, 2017 9:27 AM
  • Thanks, Angelia. This is not the case using the formula that 张文洲 proposed.

    The formula is calculated in donors' rows so it cannot be propagated to any of its active connections. 


    • Edited by gerotutu Thursday, June 8, 2017 7:59 PM
    Thursday, June 8, 2017 4:53 AM
  • Gero, any progress on this?

    Thanks!


    Ed Price, Azure Development Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Monday, October 9, 2017 8:50 PM