Forum Discussion
Helper IIIHow to filter data from 2 table based different date conditions while having only one date slicer?
Hi Experts,
I have 2 table having data as below:
Table1:
| Order Number | Order Type | Transaction Date | Modification Date | Invoice Number | Revenue |
| 12345 | OT1 | 21-Sep-24 | 22-Sep-24 | 34234234 | 13741.86 |
| 87863 | OT2 | 22-Sep-24 | 23-Sep-24 | 45342 | 1620 |
| 68469 | OT3 | 23-Sep-24 | 28-Sep-24 | 35243535 | 900 |
| 553742 | OT4 | 24-Sep-24 | 29-Sep-24 | 4523525 | 1620 |
Table 2:
| Order Number | Order Type | Modification Date | Item Number | Product code | Geography code |
| 12345 | OT1 | 22-Sep-24 | 41591074 | wqewq1 | 1 |
| 87863 | OT2 | 23-Sep-24 | 41324274 | dcsac2 | 2 |
| 68469 | OT3 | 28-Sep-24 | 41246418 | esafdsa2 | 3 |
| 553742 | OT4 | 29-Sep-24 | 36594912 | fsdfd3 | 4 |
Both the tables are joined on Order Number.
I should have a "Date Slicer" where
for table 1,the date condition should be as below:
Date_Slicer_Date > ModificationDate and Date_Slicer_Date <= Transaction Date
for table 2,the date condition should be as below:
Date_Slicer_Date > ModificationDate
So if date selected in the data slicer is 23-Sep-2024 then only record corresponding to order number 68469 should be displayed and my table visual should look like as below:
| Order Number | Order Type | Transaction Date | Modification Date | Invoice Number | Revenue | Item Number | Product code |
| 68469 | OT3 | 23-Sep-24 | 28-Sep-24 | 35243535 | 900 | 41246418 | esafdsa2 |
Can you please suggest how to achieve this scenario using only one date slicer?
Thanks and Regards,
Tushar Gaurav
8 Replies
- Selva-Salimi
Solution Sage
You can have third table (Dim Date) and select slicer from thsi table.
or the other solution is not to connet these two tables together and just using a measure ( find selected values of table 1 in table2 and use if to be able to filter them in visual level)
If this post helps, then I would appreciate a thumbs up 👍 and mark it as the solution to help the other members find it more quickly.
TusharGauravHi Selva,
Thanks for your response.
Unfortunately I am not able to understand your suggestion.
Can you please be a little bit more elaborative using the sample data that I shared.
Thanks and Regards,
Tushar Gaurav
- Selva-Salimi
Would you please clarify how you define condition of your slicer and also from which table have you choosed your date slicer?
- TusharGaurav
Hi Selva,
That is what I need to know.
The business requirement is as below:
I should have a "Date Slicer" where
for table 1,the date condition should be as below:
Date_Slicer_Date > ModificationDate and Date_Slicer_Date <= Transaction Date
for table 2,the date condition should be as below:
Date_Slicer_Date > ModificationDate
In the date slicer I am planning to use "Modification Date"
- Selva-Salimi
I recommend you to have another table which can call your Date table and the slicer on the dashboard should be from this table. to generate the dates you can create a table, insert the minimum date you need, go to transform data, click on custom column and define a new column using following formula: ( keep in mind that 1000 is number of days you need to have following the start date) and Call this table DimDate
List.Dates ([Start] , 1000 , #duration( 1, 0, 0, 0))
and then click on the generated column and select expand to new row. you also can delete other columns.
after that using this measure for first table:
measure 1 := if ( selectedvalue ('Dimdate' [Dates]) > 'table 1' ModificationDate && selectedvalue ('Dimdate' [Dates]) <= 'table 1' Transaction Date , 1 , 0 )
write another measure for table 2 as follows:
measure 2 := if ( selectedvalue ('Dimdate' [Dates]) > 'table 2' ModificationDate , 1 , 0 )
** delete the relation between your tables**
add this two measure in visual level filter of each table and set it to 1.
If this post helps, then I would appreciate a thumbs up and mark it as the solution to help the other members find it more quickly.
- TusharGaurav
Hi Selva,
I have tried the steps but its not working for me.
I am sharing the .pbix file.
Can you please check if you have bandwidth.
Thanks and Regards,
Tushar Gaurav
