topic Re: Left outer join on many-many relationship in Direct query Model in Developer

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

data_model — Tue, 23 Jul 2024 15:18:05 GMT

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

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

Anonymous — Wed, 24 Jul 2024 02:42:29 GMT

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

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

data_model — Wed, 24 Jul 2024 23:17:40 GMT

@Anonymous 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

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

Anonymous — Thu, 25 Jul 2024 09:17:24 GMT

Hi @data_model ,

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

Best Regards

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

data_model — Thu, 25 Jul 2024 17:51:21 GMT

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

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

Anonymous — Fri, 26 Jul 2024 09:37:18 GMT

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