Help needed with AND logic in nested CALCULATE formulae

[AbbasAsaria90]

Background to data structure:

Our users do an action which is called ‘publishing a work.’ In our database, this is reflected in a table where work_id is the primary key, and there are other columns called e.g., user_id, time_published

The table we have aggregated in our Data Warehouse (which is what is imported into Power BI) has columns (user_id, user_email, day, delivered). So if a user publishes n works on a 2017-12-19, there will be a row with [id, email, 2017-12-19, n] – but if a they did not publish anything on 2017-12-18, there is no row for that.

Every Thursday, we send an email to all emails who have not ‘published a work’ in the last two weeks, and we are trying to determine the effectiveness of that

I also have a table called All emails, which is just all of the emails

Problem

I have a table with dates of the emails (weekly intervals) and the following calculated columns

Not sent email =

CALCULATE(

distinctcount('All emails'[email]),
filter('All emails',
    CALCULATE(distinctcount(pobbledb_works_published[email]),
            filter(pobbledb_works_published,
            and('Days of campaigns'[email_date] >= pobbledb_works_published[delivered],
                 pobbledb_works_published[delivered] > 'Days of campaigns'[email_date] - 14) && pobbledb_works_published[email] = 'All emails'[email] )) > 0

))

Not sent email, shared in week + 1 =

CALCULATE(
distinctcount('All emails'[email]),
filter('All emails',
    CALCULATE(distinctcount(pobbledb_works_published[email]),
            filter(pobbledb_works_published,
            and('Days of campaigns'[email_date] >= pobbledb_works_published[delivered],
                 pobbledb_works_published[delivered] > 'Days of campaigns'[email_date] - 14) && pobbledb_works_published[email] = 'All emails'[email] )) > 0
    && CALCULATE(distinctcount(pobbledb_works_published[email]),
            filter(pobbledb_works_published,
            and('Days of campaigns'[email_date] < pobbledb_works_published[delivered],
                 pobbledb_works_published[delivered] <= 'Days of campaigns'[email_date] + 7) && pobbledb_works_published[email] = 'All emails'[email] )) > 0
))

Not sent email, shared in week + 1, shared in week + 2 =

CALCULATE(
distinctcount('All emails'[email]),
filter('All emails',
    CALCULATE(distinctcount(pobbledb_works_published[email]),
            filter(pobbledb_works_published,
            and('Days of campaigns'[email_date] >= pobbledb_works_published[delivered],
                 pobbledb_works_published[delivered] > 'Days of campaigns'[email_date] - 14) && pobbledb_works_published[email] = 'All emails'[email] )) > 0
    && CALCULATE(distinctcount(pobbledb_works_published[email]),
            filter(pobbledb_works_published,
            and('Days of campaigns'[email_date] < pobbledb_works_published[delivered],
                 pobbledb_works_published[delivered] <= 'Days of campaigns'[email_date] + 7) && pobbledb_works_published[email] = 'All emails'[email] )) > 0
    && CALCULATE(distinctcount(pobbledb_works_published[email]),
            filter(pobbledb_works_published,
            and('Days of campaigns'[email_date] + 7 < pobbledb_works_published[delivered],
                 pobbledb_works_published[delivered] <= 'Days of campaigns'[email_date] + 14) && pobbledb_works_published[email] = 'All emails'[email] )) > 0
 
))

Intuitively, for all dates, we would have that the value for Not sent email > Not sent email, shared in week + 1 > Not sent email, shared in week + 1, shared in week + 2 – because each column is the same as the previous, but with an addition AND logic.

However, we have that this is not the case for all weeks as shown below. 

Is there something wrong with the syntax of the formula - or is there another better way to show this?

Thanks

[v-shex-msft]

HI @AbbasAsaria90,

Can you please share some sample data or pbix file for test?

Regards,

Xiaoxin Sheng

[AbbasAsaria90]

Hi Xiaoxin,

I've uploaded some sample, anonymised data here

Instead of email addresses, each email address has been assigned a random string in the file

Thanks

[v-shex-msft · Accepted solution]

Hi @AbbasAsaria90,

You can try to use below sample file if it suitable for your requirement.(I summary you formula and use new logic to check date range).

Formula:

Spoiler

Not sent = 
VAR temp =
    ADDCOLUMNS (
        Email,
        "Exist", COUNTROWS (
            FILTER (
                ALL ( 'Detial Info' ),
                'Detial Info'[delivered]
                    IN CALENDAR ( 'Date'[Date] - 13, 'Date'[Date] )
                    && 'Detial Info'[Email_number] = Email[Email_number]
            )
        )>0
    )
RETURN
    CALCULATE (
        COUNTROWS ( VALUES ( Email[Email_number] ) ),
        FILTER ( temp, [Exist] )
    )



Not send, +1 = 
VAR temp =
    ADDCOLUMNS (
        Email,
        "Exist", COUNTROWS (
            FILTER (
                ALL ( 'Detial Info' ),
                'Detial Info'[delivered]
                    IN CALENDAR ( 'Date'[Date] - 13, 'Date'[Date] )
                    && 'Detial Info'[Email_number] = Email[Email_number]
            )
        )
            > 0,
        "Exist 2", COUNTROWS (
            FILTER (
                ALL ( 'Detial Info' ),
                'Detial Info'[delivered]
                    IN CALENDAR ( 'Date'[Date] - 1, 'Date'[Date] + 7 )
                    && 'Detial Info'[Email_number] = Email[Email_number]
            )
        )
            > 0
    )
RETURN
    CALCULATE (
        COUNTROWS ( VALUES ( Email[Email_number] ) ),
        FILTER ( temp, [Exist] && [Exist 2] )
    )



Not send,+1,+2 = 
VAR temp =
    ADDCOLUMNS (
        Email,
        "Exist", COUNTROWS (
            FILTER (
                ALL ( 'Detial Info' ),
                'Detial Info'[delivered]
                    IN CALENDAR ( 'Date'[Date] - 13, 'Date'[Date] )
                    && 'Detial Info'[Email_number] = Email[Email_number]
            )
        )
            > 0,
        "Exist 2", COUNTROWS (
            FILTER (
                ALL ( 'Detial Info' ),
                'Detial Info'[delivered]
                    IN CALENDAR ( 'Date'[Date] - 1, 'Date'[Date] + 7 )
                    && 'Detial Info'[Email_number] = Email[Email_number]
            )
        )
            > 0,"Exist 3", COUNTROWS (
            FILTER (
                ALL ( 'Detial Info' ),
                'Detial Info'[delivered]
                    IN CALENDAR ( 'Date'[Date] - 14, 'Date'[Date] - 8 )
                    && 'Detial Info'[Email_number] = Email[Email_number]
            )
        )
            > 0
    )
RETURN
    CALCULATE (
        COUNTROWS ( VALUES ( Email[Email_number] ) ),
        FILTER ( temp, [Exist] && [Exist 2] &&[Exist 3] )
    )

Regards,

Xiaoxin Sheng

[AbbasAsaria90]

Thank you - that's a really cool solution to this. I'd never looked at using VAR / defining temp tables / RETURN for calculations before