Left outer join on many-many relationship in Direct query Model

[data_model]

Hello,

I am trying to build a report on Direct query (for real-time need) based semantic model with 2 tables, and the relationship between them is many-many due to the nature of the data. We need to create a visual mimicing the below sql. i.e., display items that exists in A but missing in B (kind of look up). 

select A.*

from tbl A left outer join tbl B

    on A.ID = B.ID

where B.Name is null and A.create_dt > '2001-01-01'

I tried to create a DAX based table but it is becoming import mode and we want this to be real-time and data volume is high.

Probably due to direct query mode and many-many, unable to create a lookup column in A using RELATED DAX function. This seems simple but cannot get it to work ( i may be missing something). Appreciate any help. 

Thanks

[v-yiruan-msft · Accepted solution]

Hi @data_model ,

I created a sample pbix file(see the attachment), please check if that is what you want. You can follow the steps below to get it:

1. Create a measure as below

Flag = 
VAR _id=SELECTEDVALUE('A'[ID])
VAR _date=SELECTEDVALUE('A'[create_dt])
RETURN IF(NOT(_id IN VALUES('B'[ID]))&&_date>DATE(2001,1,1),1,0)

2. Create a table visual which the Fields options all from the table A

3. Apply a visual-level filter on the above table visual with the condition (Flag is 1)

Best Regards

[data_model]

@v-yiruan-msft  Thanks for taking time and the solution. For some reason, i couldn't be able to download your file (probably blocked in our network). But am able to borrow your code, it worked and got the result i am looking for. 

Couple of observations/questions,

Overall response time is 3-4times longer vs using my entire sql via native/direct query ( 7000ms vs 24000ms). When i looked at the generated query (via performance analyzer), PBI produced multiple queries (one for tbl1 with all the visual columns, another for tbl1 just with the id column , another for tbl2 with it's id column) and seems join/processing happening in the PBI layer. If so, it may be a problem as these tables are large.

Table visual giving the data i need, but matrix visual returns no data (i assume this may be selectedvalue function as matrix visual is just some description column and count of id).

Thanks again

[v-yiruan-msft]

Hi @data_model ,

How did you set your matrix visual?  Could you please provide the related Fields settings and screenshot? Thank you.

Best Regards

[data_model]

Here is the matrix visual setup. Both the columns in this visual are from the tbl1. Hope this helps. Thanks

[v-yiruan-msft · Accepted solution]

Hi @data_model ,

You can create a measure as below and replace [Count of ID2] with the new measure on the matrix.

Measure =
VAR _tab =
    FILTER (
        'Delta (Base)',
        NOT ( 'Delta (Base)'[ID] IN VALUES ( 'B'[ID] ) )
            && 'Delta (Base)'[create_dt] > DATE ( 2022, 1, 1 )
            && 'Delta (Base)'[create_dt] <> BLANK ()
    )
RETURN
    COUNTAX ( _tab, [ID] )

Best Regards

[v-yiruan-msft · Accepted solution]

Hi @data_model ,

I created a sample pbix file(see the attachment), please check if that is what you want. You can follow the steps below to get it:

1. Create a measure as below

Flag = 
VAR _id=SELECTEDVALUE('A'[ID])
VAR _date=SELECTEDVALUE('A'[create_dt])
RETURN IF(NOT(_id IN VALUES('B'[ID]))&&_date>DATE(2001,1,1),1,0)

2. Create a table visual which the Fields options all from the table A

3. Apply a visual-level filter on the above table visual with the condition (Flag is 1)

Best Regards