Filter Slicer on multiple condition

[ravitejaballa]

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

[amitchandak]

@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)

[ravitejaballa]

@amitchandak is there a way to do using measures ?

[ravitejaballa]

with that Expired and Expired within 90 days filter won't show

[amitchandak]

@ravitejaballa , for the measure, use the second table as an independent table and the based on selected value filter the measure using the slicer flag