Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Memory Error

hi,
I have a fact table where one of its columns stores a certain value(trafiktime). I have three other tables that are indirectly related to it, which I call them A,B C, . I have a search table that is not related to any of the tables and has 46 rows and call it LOOKUPTABLE. I want to make a report that uses a matrix, and its rows are taken from three other tables(A,B,C), and the column shows the date, which is supposed to contain the specific value of the fact table in the value field. But the report should show the rows that are the same as the columns of the lookup table.
I have written the following Measure but it gives a memory error

AVGMeasure2 =
VAR FilteredTable =
    FILTER(
        'FactTable',
        COUNTROWS(
            FILTER(
                'LookupTable',
                RELATED('A'[Number]) = 'LookupTable'[Line]
                && RELATED('B'[Direction]) = 'LookupTable'[Direction_Code]
                && RELATED('C'[Point]) = 'LookupTable'[Points]
            )
        ) > 0
    )
RETURN
    AVERAGEX(FilteredTable, 'FactTable'[trafiktime])

This is the error:

 

I would be gratefull if anybody could help me.
Show and Tell
  • rajendraongole1's avatar
    rajendraongole1
    1 year ago

    Hi Anonymous - you can create a measure establishes virtual relationships between LookupTable and tables A, B, and C using TREATAS. It filters FactTable efficiently without looping over large datasets unnecessarily.

     

    AVGMeasure2 =
    VAR FilteredFactTable =
    CALCULATETABLE(
    'FactTable',
    TREATAS(
    VALUES('LookupTable'[Line]), 'A'[Number]
    ),
    TREATAS(
    VALUES('LookupTable'[Direction_Code]), 'B'[Direction]
    ),
    TREATAS(
    VALUES('LookupTable'[Points]), 'C'[Point]
    )
    )
    RETURN
    AVERAGEX(FilteredFactTable, 'FactTable'[trafiktime])

     

    try the above one, still if issue exists use Performance Analyzer in Power BI to identify the exact steps causing memory issues.Break down the measure into smaller parts and evaluate intermediate results.

     

     

     

     

6 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Super User
    1 year ago

    Hi Anonymous - you can create a measure establishes virtual relationships between LookupTable and tables A, B, and C using TREATAS. It filters FactTable efficiently without looping over large datasets unnecessarily.

     

    AVGMeasure2 =
    VAR FilteredFactTable =
    CALCULATETABLE(
    'FactTable',
    TREATAS(
    VALUES('LookupTable'[Line]), 'A'[Number]
    ),
    TREATAS(
    VALUES('LookupTable'[Direction_Code]), 'B'[Direction]
    ),
    TREATAS(
    VALUES('LookupTable'[Points]), 'C'[Point]
    )
    )
    RETURN
    AVERAGEX(FilteredFactTable, 'FactTable'[trafiktime])

     

    try the above one, still if issue exists use Performance Analyzer in Power BI to identify the exact steps causing memory issues.Break down the measure into smaller parts and evaluate intermediate results.

     

     

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Thank you for your reply.
      The error is fixed, but the filter is not applied to the table and shows all rows

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        Hi, Anonymous 

        Glad to hear your bug was fixed.

        Based on your information, I create sample tables:

         

        Then create a new measure, try the following DAX:

        AVGMeasure = 
VAR FilteredTable =
    CALCULATETABLE(
        'FactTable',
        FILTER(
            'FactTable',
            LOOKUPVALUE('LookupTable'[Line], 'LookupTable'[Line], 'FactTable'[A_Number]) = 'FactTable'[A_Number] &&
            LOOKUPVALUE('LookupTable'[Direction_Code], 'LookupTable'[Direction_Code], 'FactTable'[B_Direction]) = 'FactTable'[B_Direction] &&
            LOOKUPVALUE('LookupTable'[Points], 'LookupTable'[Points], 'FactTable'[C_Point]) = 'FactTable'[C_Point]
        )
    )
RETURN
    AVERAGEX(FilteredTable, 'FactTable'[trafiktime])

         

        Here is my preview:

        Hopefully my example data will give you a reference

         

        How to Get Your Question Answered Quickly

        Best Regards

        Yongkang Hua

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.