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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Calculate in calculated Column

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 ?

 

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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
)

View solution in original post

3 REPLIES 3
v-yuta-msft
Community Support
Community Support

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
Not applicable

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
Not applicable

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
)

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.