DAX for Caculating for a Filtered Value During Specific Time

[Anonymous]

Hi everyone,

I'm a bit of a DAX novice, and I was hoping maybe you all would be able to help me.

I have a table with email activity data. It has a row for each action taken for each email sent. For example, for each email sent there will be a row for sent, delivered, opened, clicked, etc. Each time it is opened or clicked, it will be a new row. Each email has a unique identifier, but each action does not have a unique identifier. So for one email, each time it is opened, clicked, sent, etc., it will have the same number. I need to calculate things like open rate and click rate for specific time ranges per sender. The main issue I'm having is that sometimes an email will be opened or clicked that was not sent during the time range I'm trying to show, so open rate will be innacurate. I want to exclude emails that were not sent during the time range from my calculation, and I want to count each open and click once. Is this possible? A screenshot of my data with confidential information grayed out is below.

[Anonymous]

I actually came up with a somewhat inelegant non-DAX solution. I have a measure for distinct count of sent emails, and I just added that as a filter for every visual and set it to greater than 0. It seems to be working to filter out the emails that were sent before the reporting period. Is there anything that could go wrong with this solution that anyone can see, though?

[Icey]

Hi @Anonymous ,

---

The main issue I'm having is that sometimes an email will be opened or clicked that was not sent during the time range I'm trying to show, so open rate will be innacurate.

---

How do you judge that the email has not been sent? Is the time of "open" or "click" earlier than "sent"?

If so, try to create a measure like so:

Measure =
VAR DateTime_ =
    CONVERT ( 'Table'[Date] & " " & 'Table'[Time], DATETIME )
VAR UUID_ =
    MAX ( 'Table'[UUID] )
VAR Sent_DateTime =
    CALCULATE (
        DateTime_,
        FILTER (
            ALLSELECTED ( 'Table' ),
            MAX ( MAX ( 'Table'[UUID] ) = UUID_ && MAX ( 'Table'[EmailAction] ) = "sent" )
        )
    )
RETURN
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            MAX (
                MAX ( 'Table'[UUID] ) = UUID_
                    && MAX ( 'Table'[EmailAction] ) = "open"
                    || MAX ( 'Table'[EmailAction] ) = "click"
            )
                && DateTime_ > Sent_DateTime
        )
    )

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @Icey , sorry, I thought I had responded to this but it doesn't look like my response went through!

First of all, thank you so much for taking the time to put together that DAX for me. I don't understand VAR well, so that was way beyond something I could have created myself.

Open, click, etc. would always be after sent, so I would judge if the email had not been sent during the time reporting frame (so before the time frame) if the sent date was before the reporting time frame. Would this change your formula?

Thank you!!

[Anonymous]

Sorry, I realized I greyed out some important info. The headers for those three grayed out columns are "sender," "sender email," and "from email."