Using date field from another table to main table for filtering of data

[aatish178]

Hi Everyone,

I have a requirement where I have two tables. One table consists of a date column which I have to use for filtering of data from another(Main) table in the calculated measure expression. The main thing is there is no relationship between two tables. 

below is the sample dataset.

table 1:

Date for filtering

01 January 2020

01 February 2020

01 March 2020

01 April 2020

Main Table:

Emp ID	Emp name	DOJ
e1	Oliver	01 January 2020
e2	George	01 February 2020
e3	Noah	01 March 2020
e4	Arthur	01 April 2020
e5	Harry	01 May 2020
e6	Leo	02 May 2020
e7	Muhammad	10 May 2020
e8	Jack	18 May 2020

Here in Main Table I want only those records where DOJ IS GREATER THAN MAX of Date for filtering(from table 1) i.e., data for emp e5 to e8 alone. Can someone please help me

@quantumudit @v-jialongy-msft 

Regards,

Aatish

[Ashish_Mathur · Accepted solution]

Hi,

Here it is - without a relationship.

[aatish178]

thanks all, both the solutions are working as expected

[v-tianyich-msft · Accepted solution]

Hi @aatish178 ,

If you need to filter by a specific time, you can refer to the following results, but of course if you only need the maximum value, just change the expression:

An attachment for your reference. Hope it helps!

Best regards,
Community Support Team_ Scott Chang

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur]

Hi,

PBI file attached.

Hope this helps.

[aatish178]

Dear Ashish Sir,

Thanks for your help. However in the attached file I could see you formed a relationship between 2 tables. I want to know whether the implementation is possible without forming a relationship and just using date field from filter table may be as a variable to use in the calculated measure of main table in order to get filtered data

Aatish

[Ashish_Mathur]

You are welcome.  why do you not want to create a relationship between the 2 tables?

[aatish178]

Hi Sir,

Actually the calendar table i am forming in my actual solution it is a combination of 2 fields date1 and date2,. I.e., min of date1 and max of date2 and both the dates are important to keep, hence i am finding it difficult to which date field I should form a relationship of date from calendar. And in my old solution which is on qlik sense. The calendar table don't have any relationship with main table , and the date from calendar table is getting stored in the variable and that variable is getting used in main table measure calculation 

[Ashish_Mathur · Accepted solution]

Hi,

Here it is - without a relationship.