Count of First Transactions per Day by Identity

[littlemojopuppy]

Good morning...I'd like to ask for some help with something.  I need to create a DAX measure to calculate the distinct count of identities per day only taking into account the first transaction that identity has in any given day.

Some sample data...

The groups highlighted in green and orange...that's all the activity for two different identities on a given date for that Identity.  I need to exclude all the records except the earliest transaction for that date (theoretically an identity could appear on multiple dates!) and get the count from there.  Any ideas?

[amitchandak]

Because of ID populated I am unable to get the logic you. need But to work in the same table you use earlier. Now the conditions will control what you populate and where you populate

Eaxmple of new column

previous date = maxx(filter(price,price[Product]=earlier(price[Product]) && price[Date]<earlier(price[Date])),price[Date])
next date = minx(filter(price,price[Product]=earlier(price[Product]) && price[Date]>earlier(price[Date])),price[Date])

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Connect on Linkedin

[littlemojopuppy]

Sorry...that wasn't even close.

But this is...

This produces the desired table from which I should be counting...a single row for each identity and date, with the MarketingSourceLogID associated with that date/time.

Here's the problem: when I plug that into COUNTX to actually calculate the counts, it doesn't work.

Identity Count by Marketing Source = 
    COUNTX(
        FILTER(
            NATURALINNERJOIN(
                SUMMARIZECOLUMNS(
                    IdentityLog[IdentityId],
                    IdentityLog[CreatedDate],
                    "EarliestTime", MIN(IdentityLog[CreatedTime])
                ),
            IdentityLog
            ),
            [EarliestTime] = IdentityLog[CreatedTime]
        ),
        IdentityLog[IdentityId]
    )

 It seems syntactically correct...no errors.  But when I drop the measure into a visualization, I get this:

Googling but if anyone could provide some advice on what is wrong and how to fix...thank you!

[littlemojopuppy]

I realized it might be helpful to provide a pic of the data model.

The problem that I'm experiencing is that I have a measure called user count defined as DISTINCTCOUNT(Identities[ID]).  That works fine UNTIL I try to use that measure with Marketing Sources (from the MSL table).  Because of the M:M relationship between the two (represented by the Identity Log table) there ends up being double counting of Identities if a person is recorded in the Identity Log table more than once on a given date with different Marketing Sources.

I was able to produce code to eliminate all but the first time an Identity appeared in the Identity Log on any given day.  But that isn't helping when trying to implement a measure to correctly count without the double and triple counting.  I'd appreciate any help that anyone might be able to provide...

[marcorusso]

The code based on INTERSECT could work if you use ADDCOLUMNS/SUMMARIZE instead of SUMMARIZECOLUMNS - the latter is not supported in context transition, so it cannot be used in measures.

However, I'm worried that your approach could be very slow with a large table. The following calculation could be better in performance.

Identity Count by Marketing Source :=
VAR FirstActivity =
    CALCULATETABLE (
        ADDCOLUMNS (
            SUMMARIZE ( IdentityLog, IdentityLog[IdentityId], IdentityLog[CreatedDate] ),
            "EarliestTime", MIN ( IdentityLog[CreatedTime] )
        ),
        ALLSELECTED ( IdentityLog )
    )
VAR FilterFirstActivity =
    TREATAS (
        FirstActivity,
        IdentityLog[IdentityId],
        IdentityLog[CreatedDate],
        IdentityLog[CreatedTime]
    )
VAR Result =
    CALCULATE (
        DISTINCTCOUNT ( IdentityLog[IdentityId] ),
        KEEPFILTERS ( FilterFirstActivity )
    )
RETURN
    Result

[littlemojopuppy]

Hi Mr. Russo -

First, thank you so very much for replying to my message!  I appreciate you taking the time to help me.

My most recent version of the code to create the unique table is below...probably still not very efficient.  😐

    FILTER(
        NATURALINNERJOIN(
            GROUPBY(
                IdentityLog,
                IdentityLog[IdentityId],
                IdentityLog[CreatedDate],
                "EarliestTime", 
                MINX(
                    CURRENTGROUP(),
                    IdentityLog[CreatedTime]
                )
            ),
            IdentityLog
        ),
        [EarliestTime] = IdentityLog[CreatedTime]
    )

I copied your code verbatim into the measure as shown and it results in a count of one

Identity Count by Marketing Source = 
    VAR FirstActivity =
        CALCULATETABLE(
            ADDCOLUMNS(
                SUMMARIZE(
                    IdentityLog, 
                    IdentityLog[IdentityId], 
                    IdentityLog[CreatedDate]
                ),
                "EarliestTime", MIN(IdentityLog[CreatedTime])
            ),
            ALLSELECTED(IdentityLog)
        )
    VAR FilterFirstActivity =
        TREATAS(
            FirstActivity,
            IdentityLog[IdentityId],
            IdentityLog[CreatedDate],
            IdentityLog[CreatedTime]
        )
    VAR Result =
        CALCULATE(
            DISTINCTCOUNT(IdentityLog[IdentityId]),
            KEEPFILTERS(FilterFirstActivity)
        )
    RETURN

    Result

I'll work through the logic and see if I can figure it out.  Thank you again!

[marcorusso · Accepted solution]

My code has a mistake, a missing CALCULATE before MIN:

Identity Count by Marketing Source = 
    VAR FirstActivity =
        CALCULATETABLE(
            ADDCOLUMNS(
                SUMMARIZE(
                    IdentityLog, 
                    IdentityLog[IdentityId], 
                    IdentityLog[CreatedDate]
                ),
                "EarliestTime", CALCULATE(MIN(IdentityLog[CreatedTime]))
            ),
            ALLSELECTED(IdentityLog)
        )
    VAR FilterFirstActivity =
        TREATAS(
            FirstActivity,
            IdentityLog[IdentityId],
            IdentityLog[CreatedDate],
            IdentityLog[CreatedTime]
        )
    VAR Result =
        CALCULATE(
            DISTINCTCOUNT(IdentityLog[IdentityId]),
            KEEPFILTERS(FilterFirstActivity)
        )
    RETURN

    Result

[littlemojopuppy]

That is amazing!  Thank you so much for your help!!!  😁

[littlemojopuppy]

My reasoning has been around using SUMMARIZE.  But unfortunately that isn't working...

What I'm trying to get to is group by IdentityID, CreatedDate, Earliest Time and whatever the MarketingSourceLogID happens to be for that combination of (IdentityID, CreatedDate and CreatedTime), not to include MarketingSourceLogID as part of any grouping.  I hope I'm doing a decent job of explaining what I'm trying to get to...

Thanks in advance for any help anyone might be able to provide!