Forum Discussion
Helper IIMixed Model - Direct Query with Subset of Records to Imported Datasets
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
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.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
2 Replies
- maruthisp
Super User
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.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
dkernen2Glorious! 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.