Merge queries & relationship between 2 tables

[Anonymous]

Dear all,

I have 2 tables when apply different filters.

I want to extract all rows from TABLE A that not exist in TABLE B.

For this I used Merge Queries with left anti join.

Until now all it's fine.

But on the both tables I want to use the same date interval for example 01-15 June.

After apply slicer with date and select the interval that I want I observe that on TABLE B not apply this slicer interval.

So in TABLE A obtain only records that is in the date interval but compared with all data available from TABLE B (not only that is in the 01-15 June).

Can you please help me?

Best regards,

[v-juanli-msft]

Hi @Anonymous 

---

@Anonymous wrote:

But on the both tables I want to use the same date interval for example 01-15 June.

After apply slicer with date and select the interval that I want I observe that on TABLE B not apply this slicer interval.

So in TABLE A obtain only records that is in the date interval but compared with all data available from TABLE B (not only that is in the 01-15 June).

---

What's your purpose of this requirement?

I create a calendar table which is connected to table a but not connected to table b,

so when i use the date from "calendar" table as a slicer, it can filter table a but keep all value from table b.

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hello, Maybe I write wrong but I want to keep interval date slicer selected on both table. For example I have 10 records in table A and 5 records in table B On 5 records in table B I have 2 records outside interval date and 3 records in interval date. I expected to obtain result below: All records that's in table A and not in table B and all records from table A with date outside interval date. Keep in mind that I want to work on the both table with the same date interval. Thanks for you help. Best regards,

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

Create a table, don't create any relationship for this table

Table = CALENDARAUTO()

Add date from this table into a slicer

Create measures in Table A

lookup_value = LOOKUPVALUE('Table b'[date],'Table b'[date],MAX('Table a'[date]))

in a not b = IF([lookup_value]=BLANK(),1,0)

outside interval date = var mindate=MIN('Table'[Date]) 
var maxdate=MAX('Table'[Date]) 
return IF(MAX('Table a'[date])<mindate||MAX('Table a'[date])>maxdate,1,0)

flag = IF([in a not b]=1||[outside interval date]=1,1,0)

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.