cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

## DAX optimization of measure that measures the mean of days between transactions

I have created 2 measures,
the first one calculates the days between to 2 consecutive transactions:
DaysBetweenTransactionDates =
VAR CurrentDate = MAX(Tickets[Transaction Date])
VAR PriorDate = CALCULATE(MAX(Tickets[Transaction Date]), FILTER(ALLSELECTED(Tickets), Tickets[Transaction Date] <CurrentDate))
RETURN
IF(CurrentDate = CALCULATE(MIN(Tickets[Transaction Date]), ALLSELECTED(Tickets)),
0,
CurrentDate - PriorDate)

and the second one takes the mean of the previous measure as follows:
DaysBetweenTransactionDates Mean =
AVERAGEX(VALUES(Tickets[Transaction Date]), [DaysBetweenTransactionDates])

When I place the 2nd measure in a card it doesn't take much to visualise but when I place the m2nd measure in a table with all agents it took more than 5 minutes to visualise the mean days for all agents.
Consider that it reads a table with ~70 million rows.
What should I do to optimize the measures in order to be loaded quickly?
2 ACCEPTED SOLUTIONS
Super User

hello @phorfanos

can you please check this measure :

summary :
1.create dimdate

2.create dimagent

3.link both tables to Table4 (the table that contains the transactional data )

4. write the measure :   days since next trans - measure ( this calculates the average )

model  :

output :

``````days since next trans - measure =
var current_date =  MAX(Table4[Transaction Date])
var c =  CALCULATE(COUNT(Table4[Transaction Date]) )

var datasource =
CALCULATETABLE(
SUMMARIZE(
table4,
dimagent[Agent],
dimdate[Date]
),
ALL(dimdate),
dimdate[Date]  <=current_date
)

var datasource1 =
CALCULATETABLE(
SUMMARIZE(
table4,
dimagent[Agent],
dimdate[Date]
),
ALL(dimdate),
dimdate[Date]  <=current_date
)

var datasource2 =
datasource1,
"prev_date",
SELECTCOLUMNS(
offset(
1,
,ORDERBY(dimdate[Date], desc)
),
"prev_date",  dimdate[Date]
)
),
"diff", DATEDIFF([prev_date],  dimdate[Date],DAY)
)

var res = SUMx(datasource2 , [diff]) / c

return if( current_date in VALUES(Table4[Transaction Date]), res)``````

let ne know if it works for you .

If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution !

It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠

Super User

(just some minor changes from the above code and removing some unecessary code ) .

``````days since next trans - measure =
var current_date =  MAX(Table4[Transaction Date])
var c =  CALCULATE(COUNT(Table4[Transaction Date]) )

var datasource1 =
CALCULATETABLE(
SUMMARIZE(
table4,
dimagent[Agent],
dimdate[Date]
),
ALL(dimdate),
dimdate[Date]  <=current_date

)

var datasource2 =
datasource1,
"prev_date",
SELECTCOLUMNS(
offset(
1,
,ORDERBY(dimdate[Date], desc)
),
"prev_date",  dimdate[Date]
)
),
"diff", DATEDIFF([prev_date],  dimdate[Date],DAY)
)

var res = SUMx(datasource2 , [diff]) / c

return
res
``````

If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution !

It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠

8 REPLIES 8
Super User

hello @phorfanos

are you able to show at least a sample data for 1 transaction with the table sturcture, (a screenshot would be enough just to make sense of what you want to achieve.

Frequent Visitor

Here an example for 1 agent A with 9 transactions during the last year:

 Transaction Date Days since next transaction 7/31/2023 0 8/11/2023 11 8/14/2023 3 8/16/2023 2 8/17/2023 1 8/19/2023 2 8/30/2023 11 9/5/2023 6 9/15/2023 10

For example it took 11 days to retransact the first time (from 31st July 2023 to 11th August 2023. And then the mean days for transacton is equal to 5.11. On average, this agent makes a transaction every 5.11 days. I hope it is clearer now.

When I insert the second measure (mean) in a table with all agents, it takes a long time to get results. Ideally I would like to have the following table:

 Agent Mean of days between transactions A 5.11 B ? C ? ... ...

Community Support

Hi @phorfanos ,

I have created simple samples and you can check the results below:

``````Measure = VAR _T = ADDCOLUMNS('Table',"AVG",AVERAGEX(FILTER(ALL('Table'),[Type]=EARLIER([Type])),[Days since next transaction]))
RETURN MAXX(_T,[AVG])``````

An attachment for your reference. Hope it helps!

Best regards,
Community Support Team_ Scott Chang

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

Frequent Visitor

DaysBetweenTransactionDates Mean =
AVERAGEX(VALUES(Tickets[Transaction Date]), [DaysBetweenTransactionDates]))
works fine and gives the results that you also reproduced. The problem is that it takes a long time to give the means for all agents (over 3mins), while when I select only one agent, I get the result quite fast (either in the table or in a separate card).
So, I think the problem is in the first variable ("DaysBetweenTransactionDates", please refer to my initial post). @Daniel29195 suggested a debug of the code, so I tried the following

DaysBetweenTransactionDates =

VAR CurrentDate = MAX(Tickets[Transaction Date])

VAR PriorDate = CALCULATE(MAX(Tickets[Transaction Date]), FILTER(ALLSELECTED(Tickets), Tickets[Transaction Date] <CurrentDate))

VAR MinDate = CALCULATE(MIN(Tickets[Transaction Date]), ALLSELECTED(Tickets))

VAR Difference = datediff(PriorDate,CurrentDate,day)

VAR Result = IF(CurrentDate = MinDate, 0, Difference)

RETURN Result
but this had a minor impact to optimization..

Super User

hello @phorfanos

can you please check this measure :

summary :
1.create dimdate

2.create dimagent

3.link both tables to Table4 (the table that contains the transactional data )

4. write the measure :   days since next trans - measure ( this calculates the average )

model  :

output :

``````days since next trans - measure =
var current_date =  MAX(Table4[Transaction Date])
var c =  CALCULATE(COUNT(Table4[Transaction Date]) )

var datasource =
CALCULATETABLE(
SUMMARIZE(
table4,
dimagent[Agent],
dimdate[Date]
),
ALL(dimdate),
dimdate[Date]  <=current_date
)

var datasource1 =
CALCULATETABLE(
SUMMARIZE(
table4,
dimagent[Agent],
dimdate[Date]
),
ALL(dimdate),
dimdate[Date]  <=current_date
)

var datasource2 =
datasource1,
"prev_date",
SELECTCOLUMNS(
offset(
1,
,ORDERBY(dimdate[Date], desc)
),
"prev_date",  dimdate[Date]
)
),
"diff", DATEDIFF([prev_date],  dimdate[Date],DAY)
)

var res = SUMx(datasource2 , [diff]) / c

return if( current_date in VALUES(Table4[Transaction Date]), res)``````

let ne know if it works for you .

If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution !

It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠

Frequent Visitor

Yes, definitely this measure is way much better than mine and produces results much faster! I just replaces the 2nd line "

``var c =  CALCULATE(COUNT(Table4[Transaction Date]) )``

to

``var c =  CALCULATE(DISTINCTCOUNT(Table4[Transaction Date]) )``

But I will work on it a little bit more to optimize it further. Dear @Daniel29195 thnx a lot for your consideration and the time spent!

Super User

(just some minor changes from the above code and removing some unecessary code ) .

``````days since next trans - measure =
var current_date =  MAX(Table4[Transaction Date])
var c =  CALCULATE(COUNT(Table4[Transaction Date]) )

var datasource1 =
CALCULATETABLE(
SUMMARIZE(
table4,
dimagent[Agent],
dimdate[Date]
),
ALL(dimdate),
dimdate[Date]  <=current_date

)

var datasource2 =
datasource1,
"prev_date",
SELECTCOLUMNS(
offset(
1,
,ORDERBY(dimdate[Date], desc)
),
"prev_date",  dimdate[Date]
)
),
"diff", DATEDIFF([prev_date],  dimdate[Date],DAY)
)

var res = SUMx(datasource2 , [diff]) / c

return
res
``````

If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution !

It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠

Super User

@phorfanos  understood.

did you debug your code .

by that i mean, did you try, to first measure in the table visual,  and check if you have performance issue.

then in measure 1, try to return the variables, and check on which variable the performance issue happen .

if you want, and possible for youm i dont have a problem going via zoom, to check it with you  via a call .

best regards.

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors