locked
Count self-increments (upgrades) of an AMOUNT field in DAX RRS feed

  • Question

  • Hi community! 

    I have a short question on how to calculate the amount of increments (upgrades) on a transaction log. The model is quite simple.

    There are two tables: one is called Pledges (around 90k registers) and the other one Gifts (around 1M rows). They are related 1-to-many through Pledges[GIFT_ID]=Gifts[PLINK]. The Gift table contains month by month the succesful donations made by each pledge. 

    I want to calculate per pledge, how many times there was an increment in Gifts[AMOUNT] through time Gifts[GIFT_DT] (ascending).

    I made something already (based on another similar calculation) but it doesn't seem to be working (the calculation takes forever)

    =
    VAR table1 = Gifts
    VAR table2 = CALCULATETABLE ( VALUES ( Gifts[AMOUNT] ) )
    RETURN
        COUNTAX (
            table1,
            COUNTROWS (
                FILTER (
                    table2,
                    Gifts[AMOUNT] > EARLIER ( Gifts[AMOUNT] )
                )
            )
        )
    

    Could you please help me to solve this out?

    Many many thanks!
    Gerónimo

    Friday, August 4, 2017 5:47 PM

Answers

  • Hi Gerónimo,

    Thaks for your question.

    If I understand you correctly, you may try below DAX formula:
    =
    COUNTX (
        Gifts,
        COUNTROWS (
            FILTER (
                Gifts,
                Gifts[PLINK] = RELATED ( Pledges[GIFT_ID] )
                    && Gifts[AMOUNT] > EARLIER ( Gifts[AMOUNT] )
            )
        )
    )


    To solve your issue more efficiently, would you please share sample data and the expected results?

    Best Regards
    Willson Yuan
    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

    Monday, August 7, 2017 2:59 AM