Forum Discussion

data_model's avatar
data_model
Frequent Visitor
2 years ago
Solved

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...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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's avatar
    Anonymous
    2 years ago

    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

data_model's avatar
data_model
Frequent Visitor
2 years ago

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

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      data_model
      Frequent Visitor
      2 years ago

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

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        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