Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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:
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/
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.
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
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!
User | Count |
---|---|
49 | |
40 | |
18 | |
14 | |
13 |
User | Count |
---|---|
100 | |
55 | |
28 | |
18 | |
13 |