I am experiencing serious performance issues with a report that uses the following type of DAX formulae:
CALCULATE(
FIRSTDATE(event_table_datetime_column);
event_table_location_code_clumn IN (value1; value2);
event_table_event_code_column IN (value3; value4)
)
to compute key events for each item. There are several millions of items and about 20 events for each item. The event table consists of the item_id, the event datetime, the location code and the creator code. The datetime is expressed with the accuracy of a second.
In practice I can run the report on a few hundred thousand items only, otherwise I run out of memory. Calculating with 150 000 items it takes 4 GB of memory (when looking at the Task Manager).
I know the number one thing to do with datetime columns is to break them into date and time. Since calculations are performed for each item, I don't see this being a solution here. I guess it would be possible: finding a first datetime would then include finding the first date and then finding the first time with the extra condition on the event date matching the one that was found to be the first.
I also tried using
SUMMARIZE (event_table;
item_id;
CALCULATE(FIRSTDATE(event_table_datetime_column);
column1=true() & column2=true()))
where column1 and column2 are calculated columns in the event table with the same conditions as in the first version, that is event_table_location_code_clumn IN (value1; value2) and event_table_event_code_column IN (value3; value4). The memory usage of this version looks exactly the same as for the first version.
The logical guess is that in both cases the event table is being materialized to produce the result. How could I possibly avoid this? Any suggestions or ideas as to what I should try?
I can't share the file publically, but if someone would offer to help out, you could have a look.
