Forum Discussion
Helper IVDynamic filtering of data based on user selected date
Hie All,
I have 2 date columns from 2 different tables as shown below:
Table1:
| Date for filtering |
| 2024-04-18 |
| 2024-04-10 |
Here the single select setting is applied to this date column
Table2:
| Date of Admission | RollNo |
| 2024-04-20 | 65443 |
| 2024-04-19 | 24231 |
| 2024-04-16 | 12313 |
| 2024-04-15 | 12123 |
| 2024-04-14 | 35342 |
| 2024-04-13 | 42143 |
| 2024-04-12 | 23342 |
| 2024-04-11 | 15432 |
| 2024-04-10 | 17282 |
| 2024-04-09 | 11132 |
| 2024-04-08 | 23121 |
| 2024-04-07 | 12653 |
What I want:
Senario1: I want to show only those dates in table2 which are less than current selected date by user from table1:
for example if user selects 2024-04-18 from table1 then dates less than 2024-04-18 should be visible in the table2(2024-04-16 to 2024-04-07) along with roll no.,
Scenario2: and if user selects 2024-04-10 from table1 then dates from 2024-04-09 to 2024-04-07 should be visible in table2 along with its respective rollno.
Once this is achieved, then based on filtered result I want to calculate max of roll no., for e.g., for scenario 1 below should be the final result:
| 2024-04-13 | 42143 |
because 42143 is the max of roll no for the filtered result of scenario1
same for scenario 2 I want to calcualte max of roll no. from the filtered result., that means below record should come as output:
| 2024-04-08 | 23121 |
The result should behave dynamically based on different Table1 date selections., here for sample I have shown only 2 dates in table1.
Can someone please help in achiving the dynamic result for above use case?
Ashish_Mathur quantumudit quantumudit1 gmsamborn Anonymous
Regards,
Aatish
PBI file attached.
Hope this helps.
10 Replies
aatish178Kindly consider that table1 and table2 are connected
Hi aatish178 ,
1. Create a table that is not related to the original table and use it as a slicer.
Table3 = DISTINCT('Table1'[Date for filtering])2. Create a measure to filter the date.
MEASURE = VAR _test = CALCULATE ( MAX ( 'Table2'[Date of Admission] ), FILTER ( ALL ( Table2 ), 'Table2'[Date of Admission] < SELECTEDVALUE ( 'Table3'[Date for filtering] ) ) ) RETURN IF ( MAX ( 'Table2'[Date of Admission] ) <= _test, MAX ( 'Table2'[Date of Admission] ), BLANK () )3. Create a measure to find the maximum RollNo.
MaxRollNo = VAR SelectedDate = MAX(Table3[Date for filtering]) RETURN MAXX( FILTER( Table2, Table2[Date of Admission] < SelectedDate ), Table2[RollNo] )If your Current Period does not refer to this, please clarify in a follow-up reply.
Best Regards,
Clara Gong
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- aatish178
Dear Clara,
Thanks for your reply.
However my final expectation is to show max of roll no. Along with admission date in the table visual. That means a single row consisting of above mentioned values., but the moment I am adding max roll no. Measure and admission date in table visual it is showing me blank dates for max roll no.
Can you pls help how to fix this
- aatish178
here after selecting date for filtering., I am proparly getting filtered dates., now for this filtered result I only want to show singal highlighted row in table visual as shown below:
- Ashish_Mathur
Super User
Hi,
PBI file attached.
Hope this helps.
- aatish178
Dear Ashish,
I will check this and get back to you 🙂
- aatish178
Hi Ashish, the solution is working as expected., however once we get filtered dates in one table visual., is it possible to show only max roll no. and respective admission date along as a single row in another table visual.,
The user dont want to see other roll nos for filtered dates., their expectation is to see single max roll no., along with its admission date.
That means only highlighted value should be visible in table visual
- Ashish_Mathur
PBI file attached.
Hope this helps.
