Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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 w...
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    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
     )
v-yuta-msft's avatar
v-yuta-msft
Community Support
7 years ago

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's avatar
    Anonymous
    Not applicable
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      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
       )