Hello!
I have a measure currently that compares a customer's "first sale date" (date of their first purchase) against my current calendar table date selections. My problem is that the DAX is taking well over 200,000ms to compute.
Data Model Structure
The data model I'm using has quite a bit of relationships and tables included, but I'll confine this to just the relevant tables.
Tables:
Relationships:
Reporting Need
In my current report, I have date slicers that users can select from including Year and Month. I have a calculated column for each determining the "Current Year" and the "Current Month" so that those can be a dynamic default selection.
What I'd like to be able to do, is have a visual that displays whether a customer's "first sale date" is within the dates that have been selected in the Year and Month slicers. I can get this to work, but the performance has been terrible and the visual is nearly unusable.
DAX
My current measure is as follows:
FS Date in Selection =
VAR MaxDateSelected = MAX( vw_dim_dates[date1] )
VAR MinDateSelected = MIN( vw_dim_dates[date1] )
VAR FSDate = MAX( dim_cust[dim_dates.date1] )
RETURN
IF(
AND(
FSDate <= MaxDateSelected ,
FSDate >= MinDateSelected
) ,
TRUE() ,
FALSE()
)
I think the main issue is that my dim_cust table is not directly connected to my vw_dim_dates table through the customer's first sale date, but this would introduce another issue. A relationship like that would limit me from displaying customers whose first sale date is outside the selected date range.
Any thoughts on how I can increase the efficiency of my current measure while still meeting the reporting needs?
Thank you!
Hi there.
1. Could you please tell us the cardinalities of your tables and the directions of the relationships?
2. The measure above does not look to be the problem. It can be written simpler, though:
FS Date in Selection =
VAR MaxDateSelected = MAX( vw_dim_dates[date1] )
VAR MinDateSelected = MIN( vw_dim_dates[date1] )
VAR FSDate = MAX( dim_cust[dim_dates.date1] )
// what is dim_dates.date1 in dim_cust[dim_dates.date1]?
// is it a name of a column?
RETURN
AND(
FSDate <= MaxDateSelected ,
FSDate >= MinDateSelected
)
3. I suspect you've got a lot of bi-directional filtering in your model, haven't you?
4. Have you used the diagnostic tools to see what is really happening when you change a selection in the offending visual?
5. Have you used DaxStudio to intercept the DAX query and see its execution plan and the time spent in the FE and SE?
By the way... What does the measure do when there are many customers visible in the current context? FSDate is the maximum date across all the customers. Is this what you want? I'd suggest you check for only one customer being visible and return BLANK() or FALSE() if not.
The measure above almost surely is not the problem. I think one of the visuals calculates something from your fact table when you change the selection and this is the real culprit...
Best
D
Hello @Anonymous!
Thank you for your response, my answers are below:
The measure is put into a table that only has customer account number and their Sales as the other fields. So MAX, once put into a table with each Customer on a row, ends up giving me their specific first sale date. The sales measure that's there is just a normal SUM( ) on the fact table.
When this single measure is removed from the single visual it's on, everything loads in less than 1500ms. When the measure is on the page, everything besides the visual this measure is on loads in less than 2500ms (I'm not sure exactly why everything increases in time). I'll try the DAX Studio analyzer to see if that gives me a little more insight.
Thank you,
Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!