Forum Discussion
virtual table create with next contact columns
- 4 years ago
The problem here is that you are declaring the this_date variable at the top of the query, so it gets evaluated once at the start of the query. Whereas you really need to be evaluating it for each row.
So you should be able to more the VAR/RETURN inside the expression for the "NEXT Contact" column so that the two variables get re-calculated for each row in the result set.DEFINE VAR v1 = ADDCOLUMNS ( SUMMARIZE ( Query1, Query1[CD_MSISDN], Query1[TS_ENTERED_QUEUE] ), "NEXT Contact", var curr_CD_MSISDN = max(Query1[CD_MSISDN]) var this_date = CALCULATE(min(Query1[TS_ENTERED_QUEUE]),filter(Query1,Query1[CD_MSISDN]=curr_CD_MSISDN)) return CALCULATE(min(Query1[TS_ENTERED_QUEUE]),ALLEXCEPT(Query1,Query1[CD_MSISDN]) ,Query1[TS_ENTERED_QUEUE]>this_date ) ) EVALUATE v1another approach is to put the Next Contact calculation into a measure, then reference that from in your table.
DEFINE Measure Query1[NEXT Contact] = var curr_CD_MSISDN = max(Query1[CD_MSISDN]) var this_date = CALCULATE(min(Query1[TS_ENTERED_QUEUE]),filter(Query1,Query1[CD_MSISDN]=curr_CD_MSISDN && Query1[TS_ENTERED_QUEUE]>this_date)) RETURN this_date VAR v1 = ADDCOLUMNS ( SUMMARIZE ( Query1, Query1[CD_MSISDN], Query1[TS_ENTERED_QUEUE] ), "NEXT Contact", [NEXT Contact] ) EVALUATE v1
The problem here is that you are declaring the this_date variable at the top of the query, so it gets evaluated once at the start of the query. Whereas you really need to be evaluating it for each row.
So you should be able to more the VAR/RETURN inside the expression for the "NEXT Contact" column so that the two variables get re-calculated for each row in the result set.
DEFINE
VAR v1 =
ADDCOLUMNS (
SUMMARIZE ( Query1, Query1[CD_MSISDN], Query1[TS_ENTERED_QUEUE] ),
"NEXT Contact",
var curr_CD_MSISDN = max(Query1[CD_MSISDN])
var this_date = CALCULATE(min(Query1[TS_ENTERED_QUEUE]),filter(Query1,Query1[CD_MSISDN]=curr_CD_MSISDN))
return CALCULATE(min(Query1[TS_ENTERED_QUEUE]),ALLEXCEPT(Query1,Query1[CD_MSISDN])
,Query1[TS_ENTERED_QUEUE]>this_date
)
)
EVALUATE
v1
another approach is to put the Next Contact calculation into a measure, then reference that from in your table.
DEFINE
Measure Query1[NEXT Contact] =
var curr_CD_MSISDN = max(Query1[CD_MSISDN])
var this_date = CALCULATE(min(Query1[TS_ENTERED_QUEUE]),filter(Query1,Query1[CD_MSISDN]=curr_CD_MSISDN && Query1[TS_ENTERED_QUEUE]>this_date))
RETURN this_date
VAR v1 =
ADDCOLUMNS (
SUMMARIZE ( Query1, Query1[CD_MSISDN], Query1[TS_ENTERED_QUEUE] ),
"NEXT Contact",
[NEXT Contact]
)
EVALUATE
v1