Hello everyone
I have two table named:
1)Bookings
2)Filter
I am trying to give a condition in slice for Booking table
Booking table has a column Date
Filter table has the column From date and To date
What I am trying to achieve is I need to filter the booking table row where the Date matches the From date and To Date
I need the booking table row to filter between the from date and to date
Please help me with the expression
Solved! Go to Solution.
For a Slice, you only need an expression that "tests" if the row should be included or not. So you simply need the filter portion of the SELECT:
AND([Date] >= ANY(SELECT(Filter[From Date], TRUE)),
[Date] <= ANY(SELECT(Filter[To Date], TRUE))
It's important to know where, in your app, you are trying to apply the filtering.
If you are showing an Inline table of Bookings in your Filter Form or a Filter Detail view then the expression would be:
SELECT(Bookings[Booking ID], AND([Date] >= [_THISROW].[From Date], [Date] <= [_THISROW].[To Date]))
If instead you're showing a filtered Booking view then you will need to select the dates from the Filter table. You haven't described how you are creating filters. You could be saving and naming filters or you could be using just a single row to set temporary filter conditions. For the below, I'll assume the later - there is only a single row with temporary dates for the filter.
The expression in this case might be:
SELECT(Bookings[Booking ID], AND([Date] >= ANY(SELECT(Filter[From Date], TRUE)),
[Date] <= ANY(SELECT(Filter[To Date], TRUE))
)
)
I purposely used expressions ANY(SELECT(Filter[From Date], TRUE) to point out that you can replace "TRUE" with any filtering criteria you need to select specific Filter rows when there are many to choose from.
For a Slice, you only need an expression that "tests" if the row should be included or not. So you simply need the filter portion of the SELECT:
AND([Date] >= ANY(SELECT(Filter[From Date], TRUE)),
[Date] <= ANY(SELECT(Filter[To Date], TRUE))
@WillowMobileSys Thank you so much
User | Count |
---|---|
37 | |
31 | |
29 | |
22 | |
18 |