Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

IF function optmization

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 taki...
Data Modeling
DAX
DAX Calculation
if statement
optimize
Performance
Anonymous's avatar
Anonymous
Not applicable
6 years ago

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

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hello Anonymous!

Thank you for your response, my answers are below:

 

  1. All tables in this model are one to many. There is one bi-directional relationship, but not on any of the relevant tables. The specific relationships are as follows:
    • dim_cust(CustID) 1 --->--- * fct_cust_date(CustID)
    • vw_dim_dates(dateid) 1 --->--- * fct_cust_date(CustID)
  2. dim_dates.date1 from an expanded column within Power Query from a related database table so that I can have the Customer's first sale date rather than just the first sale date id.
  3. Just one bi-directional relationship between two other tables (A Salesperson dimension and a lookup to a table that lists when a salesperson replaced another)
  4. I used the performance analyzer within Power BI which showed me that 98% of the loading time is attributed to DAX (203,796ms out of 207,277ms total load time). When I remove this specific measure, all things the same, the total time goes down to 1,471ms (87ms DAX). This is why I suspected that the specific measure was the culprit.
  5. I've never actually used the DAX Studio query analyzer. I'll look into that.

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,

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago
    You say:

    dim_cust(CustID) 1 --->--- * fct_cust_date(CustID)
    vw_dim_dates(dateid) 1 --->--- * fct_cust_date(CustID)

    The last one is not correct, I think.

    Best
    D