Forum Discussion
How do I get values from one table, only if there is no matching data in a linked table?
Hopefully this makes sense.
I have a three table model, Details, ParentResponses, and StudentResponses. We have asked parents and students what laptop provision they have at home. We ask the same question to both groups as sometimes parents or students are awful at responding to requests for information, so hopefully we can get a better response rate by combining the two groups.
One of the questions is, what laptop provision doyou have at home (called 'provision' in both ParentResponses and StudentResponses). The Details table contains a list of every student in the school, their unique email address and their parent(s) email address. As a student can have multiple parents, there is a one to many relationship between ParentResponses and Details, and a one to many relationship between StudentResponses and Details.
What I want to do it obtain the student responses to their survey only if the parent has not submitted a response to their survey. Logically something along the lines of: If no parent response in ParentResponses table for this student, check to see if there is a response in the StudentResponses.
Hopefully that all makes sense. How can I do this?
3 Replies
- collinq
Super User
Hi Anonymous ,
There is probably some DAX you could do using the filter functions. But, it might be just as easy to append the tables together and then use a simple set of custom columns where it works something like "if there is a parent response, put a 1 in the column, otherwise 0" and then you select the columns with a 1 in them. Or you could do somethngn similar by making a measure in one table based on the response in the other table. And then, use that measure as your criteria.
I would appreciate Kudos if my response was helpful. I would also appreciate it if you would Mark this As a Solution if it solved the problem. Thanks!
v-lili6-msftCommunity Support
hi Anonymous
Sample data and expected output would help tremendously.
Please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490Regards,
Lin
As recommended, some sample data and required output is below.
ParentSurvey
ParentEmailAddress Provision [email protected] "No laptop" [email protected] "Mac laptop" [email protected] "Windows 10 laptop" StudentSurvey
ParentEmailAddress Provision [email protected] "No laptop" [email protected] "Mac laptop" [email protected] "Windows 10 laptop, Mac Desktop" Details
ParentEmailAddress StudentEmailAddress [email protected] [email protected] [email protected] [email protected] [email protected] [email protected] [email protected] [email protected] The key thing is that the DAX query needs to look at student4 because their parent (parent4) hasn't respoded to their survey.
And the output I would like:
ParentEmailAddress StudentEmailAddress Provision [email protected] [email protected] "No laptop" [email protected] [email protected] "Mac laptop" [email protected] [email protected] "Windows 10 laptop" [email protected] [email protected] "Windows 10 laptop, Mac Desktop"
