Dax measure taking up too much time/memory/resources

[_Regina]

Hi All,

so I have these tables  

Account table

Account Number

Client id

date opened

Issue table

Account Number

Date received

 They are connected via account number one to many. The issue tablke has around 10K records and accounts table has around 600K records. Ofcourse the total dataset has many other tables.

I am trying to create a matrix visual where I want Client ID and number of acc they opened after first date received. One client can have mutiple accounts. I build the measure below

but it takes up too much time and eventually I have to cancel the operation. It sucks up all the memory/disk on my machine. I am really stuck and would appreciate any guidance.

Number of Acc Opened after tag on =

var _date_rec= CALCULATE(min('Issue'[Date Received]))

return

CALCULATE(DISTINCTCOUNT(account[Account Number]),account[Date Opened]>_date_rec)

[_Regina · Accepted solution]

I made changes to the issue table , added client id to the issue table and then joined issue table to client table in the dataset and it works fine now.

[ppm1]

That's a pretty small dataset. I would have written that measure like this, but I would not have expected your version to have memory issues. Do you have another path between these two tables in your model (i.e., ambiguity)? Any bi-directional relationships? I'm assuming you the Accounts table is the 1 side, and that you are using the Client ID column from that table in your visual.

Number of Acc Opened after tag on =
VAR _date_rec =
    MIN ( 'Issue'[Date Received] )
RETURN
    CALCULATE (
        COUNTROWS ( account[Account Number] ),
        account[Date Opened] > _date_rec
    )

Pat

[_Regina · Accepted solution]

I made changes to the issue table , added client id to the issue table and then joined issue table to client table in the dataset and it works fine now.

[amitchandak]

@_Regina , try with this small chnage

Number of Acc Opened after tag on =
var _date_rec= CALCULATE(min('Issue'[Date Received]))
return
CALCULATE(DISTINCTCOUNT(account[Account Number]),filter(account, account[Date Opened]>_date_rec) )

[_Regina · Accepted solution]

I made changes to the issue table , added client id to the issue table and then joined issue table to client table in the dataset and it works fine now.