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 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,

 

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.

3 Replies

  • 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.