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 )
Basically, I'd like to be able to see the data in my Pivot Table the same way as it is in the small results table I posted, aggregating the way I described at the [day_last_mod] and [pipeline_id] levels, and even higher up, i.e., showing the sum of only the most recent values for each pipeline_event_id. Is there anything I can provide that would help?
I'm not at all sure, by the way, that I'm not over-complicating this. Maybe there's a simple measure like
CALCULATE (
LASTNONBLANK ( qryPipelineUS[amount], 1 ),
VAR earlyMod = MAX( qryPipelineUS[day_last_mod] )
RETURN
FILTER (
ALLEXCEPT ( qryPipelineUS, qryPipelineUS[pipeline_event_id] ),
qryPipelineUS[day_last_mod] <= earlyMod
)
)...that would do the job. I think I may not have my data sorted properly for LASTNONBLANK to correctly, so I'm trying that this morning.
