Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Calculate with filter causing not enough memory error

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?

Need Help

4 Replies

  • Seward12533's avatar
    Seward12533
    Solution Sage
    7 years ago
    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])))

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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's avatar
        Seward12533
        Solution Sage
        7 years ago

        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?