Hello Everyone,
TL;DR How to correctly architect my data structure to compare Actual Activity vs. Planned/Modeled Activity.
I have a datamodel where I have 4 tables, DriverProfile, FactTable, MasterCalendar and the DriverModel. The DriverModel table contains how many miles a driver is expected to drive on a certain day. The actual miles the driver drives are on the FactTable. My goal is to compare the Actual Miles driven to the DriverModel miles.
As of now, I have a bar chart that looks like this.
The dimension is: =If( [Driver Model Id] = [Driver Profile Id], [Driver Model Id] )
The measures are: Sum([Driver Model Miles])
Sum([OrderMiles])
This gives me the result I'm expecting. But when I use real data, it gets painfully slow, and the CPU hits 90+% any time the chart is rendered. I believe it has to do with the way my measure is "If( [Driver Model Id] = [Driver Profile Id], [Driver Model Id] )"
Is there a better way to do this? I'm not able to figure out how to arrive at the same result, and getting a bigger machine (4 core, 16GB currently) is out of the question.
I've attached my script and the QVF. Any help is greatly appreciated.
