Calculate in calculated Column

[Anonymous]

I am trying to find out how many calls that I have got with in the last 3 days with the name of same account number. For that I choose to create a caluculated column which finds the number of calls with the name of the same account number. My issue is I have created a calculated column for my pourpose but it's taking almost more than an hour to get the column calculated and it's working as expected. This is the DAX for the calculated column 

# of Repeats by Accnt (last 3 days)
=
VAR ACT = call_details[FINAL_ACCT_ID]
VAR DT = call_details[SHORT_START_DT]
RETURN
IF (
ISBLANK ( ACT )
|| LEN ( ACT ) <= 5,
BLANK (),
CALCULATE (
DISTINCTCOUNT(call_details[CALL_ID]),
FILTER (
CALCULATETABLE (
call_details,
ALLEXCEPT ( call_details, call_details[FINAL_ACCT_ID] )
),
call_details[SHORT_START_DT] < DT
&& call_details[SHORT_START_DT]
>= DT - 3
)
)
)

 But If I modified the above DAX as below then it's taking less than 30seconds to get the column calculated.

# of Repeats by Accnt (last 3 days)
=
VAR ACT = call_details[FINAL_ACCT_ID]
VAR DT = call_details[SHORT_START_DT]
RETURN
IF (
ISBLANK ( ACT )
|| LEN ( ACT ) <= 5,
BLANK(),
COUNTROWS (
FILTER (
CALCULATETABLE (
call_details,
ALLEXCEPT ( call_details, call_details[FINAL_ACCT_ID] )
),
call_details[SHORT_START_DT] < DT && call_details[SHORT_START_DT] >=DT-3
)
)
)

My ultimate goal is to acheive the result of 1st mentioned DAX. Is there any way that we can speed the Calculation ?

[v-yuta-msft]

Hi nvpraveenyakkal,

Obviously in your 1st dax formula, distinctcount will cost lots of time, if your dataset are very big I'm afraid the performance can't be slowed down because the algorithm in dax is naive. However, there're some other solutions which can get distinct count like values, for example, maybe you can modify your dax formula like below and check the performance:

# of Repeats by Accnt (last 3 days) =
VAR ACT = call_details[FINAL_ACCT_ID]
VAR DT = call_details[SHORT_START_DT]
RETURN
    IF (
        ISBLANK ( ACT )
            || LEN ( ACT ) <= 5,
        BLANK (),
        CALCULATE (
            COUNTROWS ( VALUES ( call_details[CALL_ID] ) ),
            FILTER (
                CALCULATETABLE (
                    call_details,
                    ALLEXCEPT ( call_details, call_details[FINAL_ACCT_ID] )
                ),
                call_details[SHORT_START_DT] < DT
                    && call_details[SHORT_START_DT]
                    >= DT - 3
            )
        )
    )

Regards,

Jimmy Tao

[Anonymous]

The above formual is returning a Circular Dependecny error. I know the Outer CALCULATE function has to perform a context transistion for every row and that's going to be costly. I am performing this on 14M rows. Can I acheive in any other way with out using outer CALCULATE function. Thanks for the help.

[Anonymous · Accepted solution]

I made mofications and finally I made this to work by using the below formula...

=
VAR DT = call_details[SHORT_START_DT]
RETURN
 CALCULATE (
 DISTINCTCOUNT ( call_details[CALL_ID] ),
 ALLEXCEPT ( call_details, call_details[FINAL_ACCT_ID] ),
 call_details[SHORT_START_DT] < DT
 && call_details[SHORT_START_DT]
 >= DT - 3
 )