cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
_Regina
Frequent Visitor

Dax measure taking up too much time/memory/resources

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)
2 ACCEPTED SOLUTIONS
_Regina
Frequent Visitor

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.

View solution in original post

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.

View solution in original post

4 REPLIES 4
ppm1
Solution Sage
Solution Sage

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

Microsoft Employee
_Regina
Frequent Visitor

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
Super User
Super User

@_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) )



Learn Power BI - Full Course with Dec-2022, with new DAX functions like Window, Index, Offset !!
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Formatted Profit and Loss Statement with empty lines

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.

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors