Forum Discussion
Integrating DAX result sets into Data Model
I finally solved this. It was dirt simple. I'm embarrassed I didn't come up with it sooner. I just had to alter my SQL query to return a difference between the current and previous row and sum all that up in DAX. My measure wound up just being:
Revenue:=VAR MaxDate = MAX ( qryPipelineUS[day_last_mod] ) RETURN CALCULATE ( SUM ( qryPipelineUS[eveAmtDiff] ), qryPipelineUS[day_last_mod] <= MaxDate )The T-SQL's a little more out of the ordinary, as you have to do partitioning and ROW windows, but it's simple enough once you get used to it.
MAX(eh.amount) OVER ( PARTITION BY ph.pipeline_id, eh.pipeline_event_id ORDER BY ph.pipeline_id, eh.pipeline_event_id, eh.date_rec_copy ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING )
After a little Photoshop work... here is the desired result. The 'Revenue' column is what the data should look like. 'TestRev' is what I'm actually getting. As you can see, 'Revenue' is showing the most recent value for any given 'pipeline_event_id' and it's that number which should roll up to the 'day_last_mod' row and the 'pipeline_id' row. So, just to walk through it, the first pipeline starts out with a total value of 84,097. The next update zeros out two of the events under that pipeline, so the total goes down to 55,197. (It also shows two events with the same value they had before. Just a quirk of the data.) Finally, all but two of the events get zero'd out and the two remaining events' values are reduced, resulting in a final value of 15,000. This is the number that rolls up to the pipeline_id level.
I finally solved this. It was dirt simple. I'm embarrassed I didn't come up with it sooner. I just had to alter my SQL query to return a difference between the current and previous row and sum all that up in DAX. My measure wound up just being:
Revenue:=VAR MaxDate =
MAX ( qryPipelineUS[day_last_mod] )
RETURN
CALCULATE (
SUM ( qryPipelineUS[eveAmtDiff] ),
qryPipelineUS[day_last_mod] <= MaxDate
)
The T-SQL's a little more out of the ordinary, as you have to do partitioning and ROW windows, but it's simple enough once you get used to it.
MAX(eh.amount) OVER (
PARTITION BY ph.pipeline_id, eh.pipeline_event_id
ORDER BY ph.pipeline_id, eh.pipeline_event_id, eh.date_rec_copy
ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING
)