Skip to main content
cancel
Showing results forย 
Search instead forย 
Did you mean:ย 

Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.

View discussion in a popup

Replying to:
cody_s
Frequent Visitor

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

 

View solution in original post