Forum Discussion
Merge queries & relationship between 2 tables
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...
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
MaggieCommunity 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.
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
Community Support
Community SupportHi 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.