Mixed Model - Direct Query with Subset of Records to Imported Datasets

[dkernen2]

Hello.  I am struggling with a relationship. I have several imported tables that refresh nightly.  I have 217 rows.  I have a Direct Query connection to another table that is live, but it only has a subset of the records.  Basically, it is an internal status - and it's only entered when there is an update.  I have a test table now that has 10 records (5 of which are null for testing).  These 10 are a subset of the 217.  How do I set up the connection?  I've tried all the different scenarios and my table either gives me duplicative rows or it only shows the 10 that have a record in the internal status table.  I want to show all 217 rows, even if the internal status table doesn't yet have a matching record.  Furthermore, if the internal status table has a record that my 217 table does not, I still don't want it to show.  Help?  Many thanks in advance.

(As an aside, if I bring in the 10 rows subset as Import, it works fine.  But I need that one source to be direct query as it will change throughout the day while the other day only updated nightly.)

Partial test workbook here: https://kauffman.box.com/s/fr0rninzuu0e4sdn27cms769mblzjefl

[dkernen2]

Glorious!  Thank you so very much.  I am pasting the solution steps below for others' benefit.  

1.Set Up the Relationship

• Create a single-direction relationship from Grant Applications[Request ID] → GRANTS CALIBRATION[Request ID] 
• Ensure the relationship is not filtering in both directions to avoid duplication.

2.Create a Measure to Simulate Left Join

Here’s a DAX measure that ensures all 217 rows from Grant Applications are shown, even if there’s no match in the DirectQuery table:

Cal Status =

CALCULATE(MAX('GRANTS_CALIBRATION'[Calibration Status]),

    TREATAS(

        VALUES('Grant Applications'[Request ID]),

        'GRANTS_CALIBRATION'[Request ID]

    )

)

3.Use Grant Applications as the Base Table in Visuals

• Use Grant Applications in your matrix or table visual.
• Add the Cal Status measure or similar to pull in values from the DirectQuery table.

[maruthisp · Accepted solution]

Hi dkernen2,

As pbix file is connecting to snowflake and can't see the sample data. But thanks for sharing the pbix file. Just for your security purpose, I would suggest always share sample data data only. Do not share the whole connection details and other major information.

I created a new page with solution steps in the below pbix file , please check it. 

DirectQueryTest.pbix

If this reply helped solve your problem, please consider clicking "Accept as Solution" so others can benefit too. And if you found it useful, a quick "Kudos" is always appreciated, thanks! 

Best Regards, 

Maruthi 

LinkedIn - http://www.linkedin.com/in/maruthi-siva-prasad/ 

X            -  Maruthi Siva Prasad - (@MaruthiSP) / X