Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Add condition to calculated column based on filter applied

Consider the following table:

 

Table1

CustomerDateNDayNo.CallIDAgentHandledFlag
A23/09/201801549870
A24/09/201811549880
A24/09/201811549891
B01/09/201801549900
B27/09/2018261549911
C14/09/201801549920
C26/09/2018121549931
C29/09/2018151549941
D29/09/201801549950
D30/09/201811549961

 

NDayNo. = Normalised day

So 0 = first time they contacted in the month.

eg Customer A first called on 23rd (=0) then again on the 4th(NdayNo or difference from 0 =1)

     Customer B first called on 1st (=0), then again on the 27th (NdayNo or difference from 0=26 )

     Customer C first called on 14th (=0), then again on the 26th (NdayNo or difference from 0 =12), then again on 29th (NdayNo or difference from 0 =15)

 

Now, I have added a filter that filters on AgentHandleFlag column (0, and/or 1)

 

Upon filtering, the resulting table appears like this:

 

Table2

CustomerDateNDayNo.CallIDAgentHandledFlag**DesiredNDayNo.**
A24/09/2018115498910
B27/09/20182615499110
C26/09/20181215499310
C29/09/20181515499413
D30/09/2018115499610

 

Now that agenthandledflag =0 rows have been removed, I need the NDayNo to re-adjust based on the new result set. So I need a new calc column that adjusts the NDayNo. so that its now based on the filtered results. (See the DesiredNDayNo. column)

 

eg. Customer A Calls on the 24th for the first time (NDayNo. now = 0), etc.

 

Is there anyway we can add conditions to calculated columns based on filters applied?

 

So from Table 1, the condition would be, if AgentHandleFlag = 0 and I change the AgentHandleFlag_filter to 1, then adjust the NdayNo. to be based on the new dataset (which is DesiredNDayNo. in Table 2).

 

I hope I have made this clear, but let me know if further clarification is required. Thanks

Need Help
Show and Tell

2 Replies

  • LivioLanzo's avatar
    LivioLanzo
    Solution Sage
    7 years ago

    If you wish to do it via calculated column then try like this:

     

     

    NDayNo Flag Based =
MAX (
    0,
    CALCULATE (
        MIN ( 'Table'[Date] ),
        ALLEXCEPT (
            'Table',
            'Table'[Customer],
            'Table'[AgentHandledFlag]
        ),
        'Table'[Date] > EARLIER ( 'Table'[Date] )
    ) - 'Table'[Date]
)
  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    7 years ago

    Hi Anonymous,

     

    Please try this measure:

    previous date =
IF (
    SELECTEDVALUE ( Table2[Date] )
        = CALCULATE (
            MIN ( Table2[Date] ),
            FILTER (
                ALLSELECTED ( Table2 ),
                Table2[Customer] = SELECTEDVALUE ( Table2[Customer] )
                    && Table2[Date].[MonthNo] = MONTH ( SELECTEDVALUE ( Table2[Date] ) )
            )
        ),
    0,
    DATEDIFF (
        CALCULATE (
            MIN ( Table2[Date] ),
            FILTER (
                ALLSELECTED ( Table2 ),
                Table2[Customer] = SELECTEDVALUE ( Table2[Customer] )
                    && Table2[Year-Month] = SELECTEDVALUE ( Table2[Year-Month] )
                    && Table2[Date] < MAX ( Table2[Date] )
            )
        ),
        MAX ( Table2[Date] ),
        DAY
    )
)

     

    Best regards,

    Yuliana Gu