How to filter data from 2 table based different date conditions while having only one date slicer?

[TusharGaurav]

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

[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]

@TusharGaurav 

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.

test.pbix

Thanks and Regards,

Tushar Gaurav

[Selva-Salimi]

you should update your first measure as follows:

measure 1 = if ( selectedvalue ('Dimdate' [Dates]) <SELECTEDVALUE('table 1'[Modification Date]) && selectedvalue ('Dimdate' [Dates]) >= SELECTEDVALUE('table 1'[Transaction Date]) , 1 , 0 )

and what I still couldn't understand is that, did you want to visualize them in one table or two tables? if one, which columns or information did you want to add this visual from table 2?!

[TusharGaurav]

Hi Selva,

Thanks for your response.

I want the information in 1 table visual.

I should have the following row:

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

Thanks and Regards,

Tushar

[Selva-Salimi]

@TusharGaurav 

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. 

[TusharGaurav]

Hi 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]

@TusharGaurav 

Would you please clarify how you define condition of your slicer and also from which table have you choosed your date slicer?