Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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).
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
21 | |
15 | |
14 | |
11 | |
7 |
User | Count |
---|---|
26 | |
24 | |
12 | |
11 | |
10 |