Forum Discussion
Memory Error
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.
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.
Hi, Anonymous
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
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: