Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
@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)
with that Expired and Expired within 90 days filter won't show
@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
User | Count |
---|---|
77 | |
74 | |
62 | |
61 | |
45 |
User | Count |
---|---|
108 | |
100 | |
91 | |
83 | |
61 |