Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
phorfanos
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

hello @phorfanos 

 

can you please check this measure : 

 

 

 

 

link to download file from : 

https://drive.google.com/file/d/1FI-fqw3eB5vGo9lA1dgTzfRR-Vi3adCz/view?usp=sharing

 

 

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  :

Daniel29195_0-1706475795740.png

 

output : 

Daniel29195_1-1706475819168.png

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 = 
ADDCOLUMNS(
ADDCOLUMNS(
    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! 🤠

 

View solution in original post

@phorfanos 

use this code instead : 

(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 = 
ADDCOLUMNS(
ADDCOLUMNS(
    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! 🤠

View solution in original post

8 REPLIES 8
Daniel29195
Super User
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.

 

 

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

Transaction DateDays since next transaction
7/31/20230
8/11/202311
8/14/20233
8/16/20232
8/17/20231
8/19/20232
8/30/202311
9/5/20236
9/15/202310

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: 

AgentMean of days between transactions
A5.11
B?
C?
......

 

Hi @phorfanos ,

 

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

vtianyichmsft_0-1705995881898.png

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.

Thank you for your reply! Actually my formula (

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..

hello @phorfanos 

 

can you please check this measure : 

 

 

 

 

link to download file from : 

https://drive.google.com/file/d/1FI-fqw3eB5vGo9lA1dgTzfRR-Vi3adCz/view?usp=sharing

 

 

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  :

Daniel29195_0-1706475795740.png

 

output : 

Daniel29195_1-1706475819168.png

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 = 
ADDCOLUMNS(
ADDCOLUMNS(
    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! 🤠

 

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!

@phorfanos 

use this code instead : 

(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 = 
ADDCOLUMNS(
ADDCOLUMNS(
    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! 🤠

@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.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.