Forum Discussion

glimpse111's avatar
glimpse111
New Member
8 years ago
Solved

Filter and slice selected column

Hi guys,   I need some help to apply filter to the following data. Some of customers are suspended from May 18 onwards so their sales number should not be included from May onwards. How can I apply...
  • Anonymous's avatar
    Anonymous
    8 years ago

    Hi glimpse111,

     

    Is there any columns store suspend date?
    If this is a case, you can write a measure to use current date ,customer name and id to find out related suspend date, then add if statement to replace records who after suspend date. Use this measure to replace value column.

     

    Sample:

    Replaced result =
VAR currDate =
    MAX ( Table[Date] )
VAR currCustomer =
    SELECTEDVALUE ( Table[Customer Name] )
VAR currID =
    SELECTEDVALUE ( Table[ID] )
VAR suspended =
    CALCULATE (
        MAX ( Table[Suspend] ),
        FILTER ( ALL ( Table ), [ID] = currID && [Customer Name] = currCustomer )
    )
RETURN
    IF (
        currDate <= suspended
            || suspended = BLANK (),
        SUM ( table[Amount] ),
        IF ( currDate > suspended, blank )
    )

     

    Regards,

    Xiaoxin Sheng

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Hi glimpse111,

 

Is there any columns store suspend date?
If this is a case, you can write a measure to use current date ,customer name and id to find out related suspend date, then add if statement to replace records who after suspend date. Use this measure to replace value column.

 

Sample:

Replaced result =
VAR currDate =
    MAX ( Table[Date] )
VAR currCustomer =
    SELECTEDVALUE ( Table[Customer Name] )
VAR currID =
    SELECTEDVALUE ( Table[ID] )
VAR suspended =
    CALCULATE (
        MAX ( Table[Suspend] ),
        FILTER ( ALL ( Table ), [ID] = currID && [Customer Name] = currCustomer )
    )
RETURN
    IF (
        currDate <= suspended
            || suspended = BLANK (),
        SUM ( table[Amount] ),
        IF ( currDate > suspended, blank )
    )

 

Regards,

Xiaoxin Sheng