Memory Error

[PegahMV]

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.

[rajendraongole1 · Accepted solution]

Hi @PegahMV - 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.

[PegahMV]

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

[v-yohua-msft]

Hi, @PegahMV 

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.

[PegahMV]

Hi,
thank you for your reply. I looked at the codes. You have added data of other columns to the fact table and made the comparison based on it. But actually, these columns are in other tables and are not directly related to it, and I don't want to add them to the fact table.

[v-yohua-msft]

Hi, @PegahMV 

If it does not help, can you please provide more details with your desired output and pbix file without privacy information (or some sample data)?

Best Regards

Yongkang Hua

[PegahMV]

Hi,
Unfortunately, I can't post the file here, so I've drawn them in Word for you so you can understand them better.

The image above is a simplified data model, of course there are relationships between them, but for simplicity I have connected them together.
I'll show you a picture of the matrix I want to have: