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 )
Community Champion"How should I go about using the data from my results set?"
- the question is what is the desired result?
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.