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

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