Bad performance with cross-sell dax measure

[Anonymous]

Hello,

I have been strugling to make the following dax measure work without taking a full 2 minutos to load.

How it works: we have 2 different group slicers in which the user chooses 2 items he wants to compare with and between a certain time period to know if clients are buying both products

Tables i will be focusing on:

Calendar Date ( day, month year etc ) 

Fct_sales Date ( has the date when the purchase occurred)

The problem:

We have 2 versions with datebetween, one where we used (calendar date table) - 3months and one where we used (Fct_Sales dates) -3months.

The same dax measure worked fine using calendar date but using fct sales date takes ages to load and we understand that this is not the best practice but intent to keep it this way if possible

Measures: with (Fct_Sales dates) -3months.

Min date Fct_Table date : Lastdate(FCT_SALES[Date])

Max date Fct_Table date : 

VAR dataref = [MAX DATE Actual]

RETURN

CALCULATE(

LASTDATE(DATEADD(REF_DT[DT_DT]; -3 ; MONTH)) ;

REF_DT[DT_DT]<= dataref)

Measure:

Clients in both groups =

VAR Group1=

CALCULATETABLE (

VALUES ( FCT_SALES[Client_ID] );

DATESBETWEEN ( calendar date[date]; [Min date Fct_Table date]; [Max date Fct_Table date] );

TREATAS ( VALUES ( 'Copy Product Table 1'[product_id] ); FCT_SALES[product_id] )

)

VAR Group2=

CALCULATETABLE (

VALUES ( FCT_SALES[Client_ID] );

DATESBETWEEN ( calendar date[date]; [Min date Fct_Table date]; [Max date Fct_Table date] );

TREATAS ( VALUES ( 'Copy Product Table 2'[product_id] ); FCT_SALES[product_id] )

)

RETURN

COUNTROWS ( INTERSECT ( Group1; Group2 ) )

Hopefully you understand what im trying to achieved

Thanks in advance 

[Anonymous]

A solution to the problem has been attached. Please have a look at the file. Adjust it to your needs but take a good look at the measures. I do not use INTERSECT to calculate the clients in both groups.

The dataset contains 500 000 rows of sales data and is pretty performant.

Best

D

[Anonymous]

Thanks, i will give it a look 

[Anonymous]

Hi there.

First off, you've not explained what is REF_DT[DT_DT].

Secondly, time-intel functions work correctly ONLY on true Date tables and should never be used directly on a fact table unless... you want to produce wrong numbers without even knowing it.

Thirdly, you should understand that slicing and dicing should NEVER be performed on fact tables directly. Always through dimensions.

Fourthly, TREATAS is never as fast as relationships which you should have in the model between your fact table and the two product tables, where - of course - one of the relationship should be inactive.

Lastly... DAX is fast only on good models (that means a star/snowflake schema). If the model is crap, no amount of DAX will make it better and faster. Easy as that.

To solve the problem you have there are many approaches. I'd like to first see the structure of your model, if you don't mind.

Thanks.

Best
D

[Anonymous]

Hello thanks for taking time to responde.

1) REF_DT[DT_DT] i forgot to change that one, but its the "calendar[date)

2 and 3) i actually using calendar table to filter between timelines and only using the dates in my fact table to establish a start and finish point.

4) i switched to "userelationship" but takes the same amount of time

I managed to find a workaround, its not perfect and wont be much use but works for now.

[Greg_Deckler]

Very difficult to troubleshoot DAX performance issues without the data and such. However, I can already tell you that you probably don't need the VALUES statement and I seriously question TREATAS but without knowing your data and model it's very difficult to say anything definitively. See these articles:

• https://community.powerbi.com/t5/Community-Blog/Performance-Tuning-DAX-Part-1/ba-p/976275
• https://community.powerbi.com/t5/Community-Blog/Performance-Tuning-DAX-Part-2/ba-p/976813
• https://community.powerbi.com/t5/Community-Blog/Soapbox-Series-Adding-No-VALUE-S/ba-p/1059818