Forum Discussion

Fiala's avatar
Fiala
Helper II
4 years ago
Solved

virtual table create with next contact columns

Hi, I'm already desperate. I need advice on the next task. I want to create a virtual table. In the table, create a new column named Next Contact. I want to know when the number in the CD_MSISDN column called the next one again if it called. I can't find the right results. 

 

I try more measures.. Fox example.
DEFINE
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))
    VAR v1 =
        ADDCOLUMNS (
            SUMMARIZE ( Query1, Query1[CD_MSISDN], Query1[TS_ENTERED_QUEUE] ),
            "NEXT Contact",
                CALCULATE (
                    CALCULATE(min(Query1[TS_ENTERED_QUEUE]),ALLEXCEPT(Query1,Query1[CD_MSISDN])
                   ,Query1[TS_ENTERED_QUEUE]>this_date)
                )
        )

EVALUATE
v1
This returns. 

Red is an example of the correct result. I think the problem is in setting the correct filter context for the variables "Curr_CD_MSISDN" and "this date". But I can't solve it. Can anyone please advise me?




DAX
Virtual Table
  • d_gosbell's avatar
    d_gosbell
    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
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

     

2 Replies

  • d_gosbell's avatar
    d_gosbell
    Super User
    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
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

     

  • Fiala's avatar
    Fiala
    Helper II
    4 years ago

    d_gosbell 

    Thank you for your help. The calculation needed a little adjustment but I'm already getting the right results. It never occurred to me that there might be such a problem.

    DEFINE
        VAR v1 =
            ADDCOLUMNS (
                SUMMARIZE ( Query1, Query1[CD_MSISDN], Query1[TS_ENTERED_QUEUE] ),
                "NEXT Contact",
                    VAR curr_CD_MSISDN =
                        MAX ( Query1[CD_MSISDN] )
                    RETURN
                        CALCULATE (
                            MIN ( Query1[TS_ENTERED_QUEUE] ),
                            ALLEXCEPT ( Query1, Query1[CD_MSISDN] ),
                            Query1[TS_ENTERED_QUEUE] > EARLIER(Query1[TS_ENTERED_QUEUE]))
                        )
            

    EVALUATE
    v1