Get count of first interaction per day

[cody_s]

Hello. A dax question for everyone.

Two real tables my interactionFact (with all the messages) and a siteDim (with the list of sites). 

Essentially would like a count of the number of times each site interacts first. In whatever the given filter context is. What think should happen is A. Get list of dates (in current context), B. Get list of sites (in current context). C. Iterate each date, for each date get ALL interactions sent for that day. Clearing all the filters except the date D. Get first interaction for the day. E. Check if site in list of sites in filter F. Return 1 if so, if not

What has me stuck is checking if the sitr is in the list. need to clear the filter to get the first interaction so that it's not the first interaction of that site for the day, but all sites of that day. I can't wrap my head around it though in DAX.

I think there is two ways of going about it. Either getting the top row (via topn(1, interactionfact, interactionfact[timestamp], 1)), then returning a 1 if the site name is in the list of visible sites in the filter. Or getting the minimum time and calculating a table containing just those minimum times with a filter on the sites and getting the row count.

Any thoughts? This the Dax have outlined. I'm stumped on the inner calculate. More of a python user, so this functional stuff is a bit of a thing to get use to. 

var _dates = VALUES(interactionFact[Date])

var _sites = VALUES(SiteDim[Name])

var result = SUMX(

    _dates,

    CALCULATE(

        ???

    )

)

..

[cody_s · Accepted solution]

Nevermind i have come to a solution for this!

there is an additional filter to keep the first interaction to after 3 am

First Sender = 
//sites visible in the current filter
VAR _sites =
    CALCULATETABLE (
        VALUES ( SiteDim[Name] )
    )
VAR _dates =
    CALCULATETABLE (
        VALUES ( InteractionFact[Date] ),
        TimeDim[Hour] > 3
    ) //dates where the first interaction is after 3 am - remove all the filter but the date range
VAR first_sender_table =
    ADDCOLUMNS (
        //iterate over dates
        _dates,
        "First Sender",
            CALCULATE (
                // applying the filter only returns the first AFTER 3 am
                VAR filt =
                    FILTER ( InteractionFact, RELATED ( TimeDim[Hour] ) > 3 )
                VAR _first_time =
                    TOPN ( 1, filt, InteractionFact[Time], 1 )
                VAR _b =
                    MAXX ( _first_time, RELATED ( SiteDim[Name] ) )
                RETURN
                    _b,
                ALLEXCEPT ( InteractionFact, InteractionFact[Date] )
            )
    ) 
    
var _filter = FILTER(first_sender_table, [First Sender] IN _sites)

var result = countrows(_filter)

RETURN
    result

[amitchandak]

@cody_s , Make sure along with date time you have only date column. to get the first transaction of day

create a measure like

calculate(countrows(Table), filter(Table, Table[datetime] = calculate(max( Table[datetime] ), filter(allselected(Table),Table[Date] =max(Table[date])))))

Date = [datetime].date
or
Date = date(year([datetime]),month([datetime]),day([datetime]))

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.