Forum Discussion
Slice based on a user selection
Customers
ID StartDate EndDate
1 20/01/23 30/02/23
2 14/04/22 23/09/22
3 03/03/22
I have above table which I want to filter by options Active or All. Active or All depends on a Date range slice. So, there is a separate single column Customer Type table with two values and a seperate Calender table. I have added a single selection Dropdown slice using that Customer Type and Date Range using Calender. Now, based on the user selection, I want to filter the Customers table based on following logic.
IF selected Customer Type is Active all the records that returns true to following condition should be kept.
Customers[StartDate] >= FIRSTDATE(Calender[Date]) &&
(
Customers[EndDate] >= LASTDATE(Calender[Date]) ||
ISBLANK(Customers[EndDate])
)
IF the selected Customer Type is All,
Customers[StartDate] >= FIRSTDATE(Calender[Date]) && Customers[StartDate] <= LASTDATE(Calender[Date]) &&
Customers[EndDate] >= FIRSTDATE(Calender[Date]) && Customers[EndDate] <= LASTDATE(Calender[Date]) &&
(
Customers[StartDate] < FIRSTDATE(Calender[Date]) && (Customers[EndDate] > LASTDATE(Calender[Date]) || ISBLANK(Customers[EndDate]))
)
The logic is now quite important but the point I'm trying to make is, I want to slice based on the user selection of Active or All not otherway around like based on some conditions, generate a column that contains either Active or All using which I could create a slice
- Anonymous3 years ago
Hi srineshnisala ,
You can use Enter data to create the following table as a slicer:Create measure.
Flag= SWITCH( TRUE(), Customers[StartDate] >= FIRSTDATE(Calender[Date]) && ( Customers[EndDate] >= LASTDATE(Calender[Date]) || ISBLANK(Customers[EndDate]) ) ,1, Customers[StartDate] >= FIRSTDATE(Calender[Date]) && Customers[StartDate] <= LASTDATE(Calender[Date]) && Customers[EndDate] >= FIRSTDATE(Calender[Date]) && Customers[EndDate] <= LASTDATE(Calender[Date]) && ( Customers[StartDate] < FIRSTDATE(Calender[Date]) && (Customers[EndDate] > LASTDATE(Calender[Date]) || ISBLANK(Customers[EndDate])) ) ,1,0)Place [Flag]in Filters, set is=1, apply filter.
When the conditions are met, only the data that meets the conditions will be displayed.
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
1 Reply
- AnonymousNot applicable
Hi srineshnisala ,
You can use Enter data to create the following table as a slicer:Create measure.
Flag= SWITCH( TRUE(), Customers[StartDate] >= FIRSTDATE(Calender[Date]) && ( Customers[EndDate] >= LASTDATE(Calender[Date]) || ISBLANK(Customers[EndDate]) ) ,1, Customers[StartDate] >= FIRSTDATE(Calender[Date]) && Customers[StartDate] <= LASTDATE(Calender[Date]) && Customers[EndDate] >= FIRSTDATE(Calender[Date]) && Customers[EndDate] <= LASTDATE(Calender[Date]) && ( Customers[StartDate] < FIRSTDATE(Calender[Date]) && (Customers[EndDate] > LASTDATE(Calender[Date]) || ISBLANK(Customers[EndDate])) ) ,1,0)Place [Flag]in Filters, set is=1, apply filter.
When the conditions are met, only the data that meets the conditions will be displayed.
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly