Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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...
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    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.

v-juanli-msft's avatar
v-juanli-msft
Icon for Community Support rankCommunity Support
7 years ago

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's avatar
    Anonymous
    Not applicable
    7 years ago
    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's avatar
      v-juanli-msft
      Icon for Community Support rankCommunity Support
      7 years ago

      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.