A lot of IF and OR statements - Making it run slow

[EmilZPM]

Hi all,

I am having some issues with the performance of some of my reports, due to some measures running slow.

Some base info:
I am connected to an Azure Blob Storage, collecting data in CSV format.
I do some cleaning using Power Query

The measure(s) in question:
Newsletter Permissions

For my company there are some different criteria a recipient have to have, in order to be have a newsletter permission, and they are: 
If any of the following are true:

IF(
   [ComplaintCheck]=1 ||
   [LastEmailCheck]=1 || (It's more than 365 days since you last got an email)
   [MalformedCheck]=1 ||
   [ValidEmailCheck]=1 ||
   [UnsubscribedCheck]=1
)

OR if ALL of the following are true or BLANK:

IF(
   Created more than 365 days ago
   First use of app is more than 365 days ago
   Your last app use is more than 1000 days ago
   Your last interaction is more than 1000 days ago
   Your last web use is more than 1000 days ago
   Your permission is set within the last 365 days
)

For all of the above I have made a seperate measure looking like this:

IF(
   OR(
       NOT(SUM('agillic-targetgroup'[Days Since First Use])<365),
      SUM('agillic-targetgroup'[Days Since First Use])=BLANK()
   )
   ,1
   ,0
)

I have used Power Query to created columns that calculate how many days it has been since the different dates.

I can provide a sample set if needed.

I hope some of you can help.

[SamInogic]

Hi @EmilZPM ,

You can try following below steps,

1. Create measures for each condition:

2. Create measures for ComplaintCheck, LastEmailCheck, MalformedCheck, ValidEmailCheck, and UnsubscribedCheck. These measures should return 1 if the condition is true and 0 otherwise.

3. Create measures for DaysSinceLastEmail, DaysSinceCreated, DaysSinceFirstAppUse, DaysSinceLastAppUse, DaysSinceLastInteraction, and DaysSinceLastWebUse. These measures should calculate the number of days since the corresponding events occurred.

4. Create a measure for PermissionSetWithin365Days to check if permission is set within the last 365 days.

DAX for these will be as follows:

ComplaintCheck = IF([ComplaintCheck]=1, 1, 0)
LastEmailCheck = IF([LastEmailCheck]=1 || [DaysSinceLastEmail] > 365, 1, 0)
MalformedCheck = IF([MalformedCheck]=1, 1, 0)
ValidEmailCheck = IF([ValidEmailCheck]=1, 1, 0)
UnsubscribedCheck = IF([UnsubscribedCheck]=1, 1, 0)
DaysSinceCreated = DATEDIFF([CreatedDate], TODAY(), DAY)
DaysSinceFirstAppUse = DATEDIFF([FirstAppUseDate], TODAY(), DAY)
DaysSinceLastAppUse = DATEDIFF([LastAppUseDate], TODAY(), DAY)
DaysSinceLastInteraction = DATEDIFF([LastInteractionDate], TODAY(), DAY)
DaysSinceLastWebUse = DATEDIFF([LastWebUseDate], TODAY(), DAY)
PermissionSetWithin365Days = IF([PermissionDate] >= TODAY() - 365, 1, 0)

Create measure with below DAX for logical operations,

NewsletterPermissions = IF( [ComplaintCheck] + [LastEmailCheck] + [MalformedCheck] + [ValidEmailCheck] + [UnsubscribedCheck] > 0, 1, IF( [DaysSinceCreated] > 365 && [DaysSinceFirstAppUse] > 365 && [DaysSinceLastAppUse] > 1000 && [DaysSinceLastInteraction] > 1000 && [DaysSinceLastWebUse] > 1000 && [PermissionSetWithin365Days] = 1, 1, 0 ) )

Thanks!

Inogic Professional Service Division

An expert technical extension for your techno-functional business needs

Power Platform/Dynamics 365 CRM

Drop an email at crm@inogic.com

Service:  http://www.inogic.com/services/ 

Power Platform/Dynamics 365 CRM Tips and Tricks:  http://www.inogic.com/blog/

[EmilZPM]

Doing the check with "+" istead of or, helped.

"IF( [DaysSinceCreated] > 365 && [DaysSinceFirstAppUse] > 365 && [DaysSinceLastAppUse] > 1000 && [DaysSinceLastInteraction] > 1000 && [DaysSinceLastWebUse] > 1000 && [PermissionSetWithin365Days] = 1"
This does not work, because for all of them they can either be BLANK or older tham 365 or 1000 days.

[v-yaningy-msft]

Hi, @EmilZPM 

Based on your description, you can try the following measure to see if it works

Measure =
IF (
    [ComplaintCheck] + [LastEmailCheck] + [MalformedCheck] + [ValidEmailCheck] + [UnsubscribedCheck] > 0,
    1,
    IF (
        ( [DaysSinceCreated] > 365
            && [DaysSinceFirstAppUse] > 365
            && [DaysSinceLastAppUse] > 1000
            && [DaysSinceLastInteraction] > 1000
            && [DaysSinceLastWebUse] > 1000
            && [PermissionSetWithin365Days] = 1 )
            || (
                ISBLANK ( [DaysSinceCreated] ) && ISBLANK ( [DaysSinceFirstAppUse] )
                    && ISBLANK ( [DaysSinceLastAppUse] )
                    && ISBLANK ( [DaysSinceLastInteraction] )
                    && ISBLANK ( [DaysSinceLastWebUse] )
                    && ISBLANK ( [PermissionSetWithin365Days] )
            ),
        1,
        0
    )
)

If this does not work, could you please share some sample data without sensitive information and expected output.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[EmilZPM]

Unfortunately it is still very heavy, and sometimes times out on the refresh.

I have shared a sample dataset and a power bi file containing the measures I have made.
The sample is only approx 4k lines, where the full data is 1m lines.

https://drive.google.com/file/d/16tDp3ph5UbVx_gv4qkzORECxYMQ60kkt/view?usp=sharing

https://drive.google.com/file/d/17vWtcnHGDqiHoB4Vvh8kGMh8x-1cpDxy/view?usp=sharing

Let me know if this isn't enough.

And thanks for the help!