IF function optmization

[CRamirez]

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:

• dim_cust (customer table)
• fct_cust_date (daily customer sales)
• vw_dim_dates (view of our date table with a "Current Date" column added to circumvent UTC time shenanigans)

Relationships:

• dim_cust (CustID) to fct_cust_date (CustID)
• fct_cust_date (dateid) to vw_dim_dates (dateid)

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!

[Anonymous]

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

[CRamirez]

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]

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

[Mariusz]

Hi @CRamirez 

Can you create a data sample and an example of an outcome?

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn