The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hello,
I'm attempting to count the Average number of emails customers customers receive in their first N months. To do this, I am defining their MIN date, and shifting this on a number of months using EDATE and a "what if parameter" that provides the number of months.
My measure works when filtering by customers, but when it computes the average over all customers, it is using the MIN date, regardless of customer (i.e. the first date in the table), rather than using each customer's MIN date to compute the average - Despite being wrapped in an averagex.
I'm hoping this is an easy oversight in my filter context, and will try to highlight my issue in screenshots, with the DAX published at the bottom of this post.
With the screenshots, please see the mmm_yyyy table in the middle of the image, with no customers selected I would expect this to have all months & an average number of emails per any customer whose first email was in that month. Instead it only shows only September & October 2016 as my data begins on 01/09/2016.
With Customers selected:
With no (or all)) Customers Selected:
// Provide customers to be iterated through during Averagex
VAR selected_customers =
ALLSELECTED( 'Campaigns'[email] )
RETURN
AVERAGEX (
selected_customers,
// create min date for each customer - removing filters for product, campaign type, campaign outcome & date, keeping customer email context
// EDATE shifts the min date on a specified number of months
VAR date_from_sign_up =
CALCULATE (
EDATE ( MIN ( 'Campaigns'[date_key] ), [Number of Months Value] ),
REMOVEFILTERS ( 'Campaigns' ),
VALUES ( 'Campaigns'[email] )
)
// Count all emails before the date specified above
VAR calc =
CALCULATE (
[No of Recipients],
KEEPFILTERS ( 'Campaigns'[date_key] <= date_from_sign_up )
)
RETURN
calc
)
try
num emails =
// Provide customers to be iterated through during Averagex
VAR selected_customers =
ALLSELECTED ( 'Campaigns'[email] )
RETURN
AVERAGEX (
selected_customers,
// create min date for each customer - removing filters for product, campaign type, campaign outcome & date, keeping customer email context
VAR CurrentCustomer = 'Campaigns'[email] // EDATE shifts the min date on a specified number of months
VAR date_from_sign_up =
CALCULATE (
EDATE ( MIN ( 'Campaigns'[date_key] ), [Number of Months Value] ),
REMOVEFILTERS ( 'Campaigns' ),
TREATAS ( { CurrentCustomer }, 'Campaigns'[email] )
) // Count all emails before the date specified above
VAR calc =
CALCULATE (
[No of Recipients],
KEEPFILTERS ( 'Campaigns'[date_key] <= date_from_sign_up )
)
RETURN
calc
)
@johnt75 - Thanks for the assistance, I would love to tell you if this solution has worked, but I'm afraid it is giving me a timeout error on the visuals. I see what you're trying to do though.
Can you think of anything else I can test?
Using your idea I've tried various other options of defining the CurrentCustomer (SELECTEDVALUE, VALUES, SUMMARIZE) but these have either produced a similar problem (SELECTEDVALUE, VALUES), or have not worked (SUMMARIZE).
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the August 2024 Power BI update to learn about new features.
User | Count |
---|---|
18 | |
18 | |
17 | |
15 | |
13 |
User | Count |
---|---|
36 | |
34 | |
19 | |
18 | |
18 |