Forum Discussion

AbbasAsaria90's avatar
8 years ago
Solved

Help needed with AND logic in nested CALCULATE formulae

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

 

 

 

  • Anonymous's avatar
    Anonymous
    8 years ago

    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

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable

    HI AbbasAsaria90,


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

     

    Regards,

    Xiaoxin Sheng

    • AbbasAsaria90's avatar
      AbbasAsaria90
      Helper I

      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

      • Anonymous's avatar
        Anonymous
        Not applicable

        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