Forum Discussion

ravitejaballa's avatar
ravitejaballa
Helper III
4 years ago

Filter Slicer on multiple condition

Hi,

 

I have data like below and I have a slicer (Expired, Expiring Within 90 Day, Terminated) using which I should filter data
Expired = Should show all records where the End date is less than today
Expiring Within 90 Day = Should show all records where End date >= today and <= 90 days
Terminated = Should show all records where State is inactive

Customer Start Date End Date State
C1 3-Jan-21 3-Feb-22 active
C2 20-Jun-20 20-Jun-21 inactive
C3 5-Jan-22 5-Jan-23 active
C4 10-Feb-22 10-Feb-23 inactive
C5 25-Oct-22 25-Oct-23 active
C6 5-Mar-21 5-Mar-22 active
C7 15-May-21 15-May-22 inactive

 

SlicerIndex Description
1 Expired
2 Expiring Within 90 Day
3 Terminated

 

 

 

 

Slicer Flag = 
var EndDateDiff = DATEDIFF(NOW(), Data[End Date], DAY)

var terminated = IF(Data[State] = "inactive", TRUE(), FALSE())
var within90days = IF(AND(EndDateDiff > 0, EndDateDiff <= 90), TRUE(), FALSE())
var expried = IF(EndDateDiff < 0, TRUE(), FALSE())

RETURN
IF(expried, 1, 
    IF(within90days, 2,
        IF(terminated, 3, 0)
    )
)

 

 

 

 

 Created a calculated Column (Slicer Flag) and added a relation.

 

problem: Expired and Expired in 90 days records state can be inactive. when I filter by "Terminated" I am getting only one record even we have 2 more records in an inactive state as Slicer Flag is overridden by Expired and Expired in 90 days flag

 

Need Help

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    ravitejaballa , try with this change

     

    Slicer Flag = 
var EndDateDiff = DATEDIFF(NOW(), Data[End Date], DAY)

var terminated = IF(Data[State] = "inactive", TRUE(), FALSE())
var within90days = IF(AND(EndDateDiff > 0, EndDateDiff <= 90), TRUE(), FALSE())
var expried = IF(EndDateDiff < 0, TRUE(), FALSE())

RETURN
Switch( True() ,
terminated, 3, 
expired,1 ,
within90days,2, 
0)