Forum Discussion
Left outer join on many-many relationship in Direct query 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
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
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
5 Replies
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
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
Hi data_model ,
How did you set your matrix visual? Could you please provide the related Fields settings and screenshot? Thank you.
Best Regards
Here is the matrix visual setup. Both the columns in this visual are from the tbl1. Hope this helps. Thanks
