Calculate with filter causing not enough memory error

[amulder]

Hi,

I have the following calculated column, that will calculate correctly and quickly.

Column1 = CALCULATE(
COUNTROWS(Query1),
FILTER(Query1,Query1[STATUS]="LN"),
FILTER(Query1,Query1[VALID]="VALID"),
FILTER(Query1,Query1[LOGIN_TIME]=Date[START]))

As soon as I change the calculated column to the following I get not enough memory errors and it never calculates.

Column1 = CALCULATE(
COUNTROWS(Query1),
FILTER(Query1,Query1[STATUS]="LN"),
FILTER(Query1,Query1[VALID]="VALID"),
FILTER(Query1,Query1[LOGIN_TIME]>=Date[START]))

I really need the calculation to be >=.  How can I re-write the calculation so that it will actually calculate?

[Seward12533]

Rather than individual filter commands try something like this. However I think the problem is more in the model. Also note a calculated column is only evaluated when you open the file or you refresh the data. And In the table the filter context is limited to that row. What table is this column in how is it related to the date table?

Please share more information about your data tables and model and what your trying to accomplish. Ideally provide a link to a PBIX with representative data

Column1 = CALCULATE(
COUNTROWS(Query1),
FILTER(Query1,[STATUS]="LN"&&[VALID]="VALID"&&[LOGIN_TIME]>=MAX(Date[START])))

[amulder]

Thanks for your response.  Unfortunately it does not resolve the issue.

I can't post the PBIX file as is it confidential information.  But here are some additional details.

I have two tables, Query 1 and Date.  I have tried have them unrelated and related.  The Start column is common between them.  They are currently unrelated while I am trying these calculations.

My Query 1 table is both wide and long.  There are 50ish columns and 2 million rows.  The data is all necessary.

My Date table is 3 columns and 200,000 rows, and again all the data is necessary.

What I want my calculated column to do is to count the number of times that a record in Query1 matches the filter criteria.  My Date table lists Dates broken into 15 minute chunks for several years.

Does this help?

[Seward12533]

If you don't want to take the time to build some repreentative samples of your data tables, You should be able to share a picture of your data model (with relationships), column headers from your Tables, and Measure definitions.   There should be nothing confidential there. 

I at lest woudl have to see your data table layout.  In geneal TALL is not a big deal but WIDE is very resource intensive.  I would look at using lookup tables. Say you table contains Customer Number, Customer Name, Customer Adress, Phone, etc.. Product Name, Product Number, etc... this could all be stored in a lookup tables that is wider but short unless you have millions of customers.

I'm sure we can help with DAX but really need more information to help you. One very important piece is this Calulcated column in Query1 table?

[amulder]

I'm not sure how to post a sample PBIX file, so here are some more details and images

There are 2 tables; Query1 and Date.  My Query1 table contains all of my login/logout times.  My Date table is all of the dates that I need to calculate how many login/logout events happened broken out by 15 minute intervals throughout each day.  These 2 tables are not related, and cannot be related (as far as I can tell).  I need to do 3 calculations:

1. Count the number of units that logged in during the 15 minute time interval

2. Count the number of units that were previously logged in and have not yet logged out during the 15 minute time interval

3. Count the number of units that logged out during the 15 minute time interval

I cannot get this to work without running into out of memory errors using the DAX formulas above.  Please have a look and let me know if there is a better way to write the formulas or go about calculating this.  In my Query1 table I have 2.1 million records, in my Date table I have 4000 records.  I can't reduce the number of records as they are all required for statistical purposes.  I have tried created a sub table of Query1 with far less columns and am still running into memory errors.

Query1Date