DAX - Is it possible to query one table and use the results to query another table?

[83-guy]

Hello,

I am new to Power BI and am trying to learn my way around it. Is it possible to query one table and use the results from that query to query another table based on the key (in this case the ID)? For example, I have these two tables as a sample. Could I find everyone in Table1 that has a yes under "Has a pet" and then use the results to then grab all of their email addresses based on the ID in table2?

Would some other system work better for this?

[Ashish_Mathur]

Hi,

Assuming no entry in the ID column of Table2 repeats, write this calculated column formula in Table1

E-mail address = if('Table1'[Has a pet])="Yes",related('Table2'[Email address]),blank())

Hope this helps.

[Bipin-Lala]

Hi @83-guy,

Yes, it's absolutely possible to query one table and use the results to query another table based on a key (such as an ID). You can achieve this by creating relationships between tables.

• Create a relationship between Table 1 and Table 2 based on the ID column. The ID column will help you uniquely identify Name in Table 1 and corresponding Email & Phone in Table 2

• Once you have created the relationship, you can drag fields from both the tables together
  
• You can apply a filter on the Has A Pet column to filter and display only people having pets

Hopefully, this solves your problem. If you are looking for some specific results or have different requirements, feel free to post here and I will try to help.

[83-guy]

Is this possible in a DAX query? Essentially, I wish to have power automate run a query to grab the email addresses.

[v-yilong-msft · Accepted solution]

Hi @83-guy ,

I’d like to acknowledge the valuable input provided by @Ashish_Mathur and @Bipin-Lala . Their initial ideas were instrumental in guiding my approach. However, I noticed that further details were needed to fully understand the issue.  

In my investigation, I took the following steps: 

I create two tables as you mentioned.

I think you can create a measure, it will help you get what you want.

Measure = 
VAR _ID =
    MAX ( 'Table 1'[ID] )
VAR _vtable =
    SELECTCOLUMNS (
        'Table 2',
        "_ID2", 'Table 2'[ID],
        "_Email", 'Table 2'[Email Address],
        "_Phone", 'Table 2'[Phone #]
    )
RETURN
    IF (
        SELECTEDVALUE ( 'Table 1'[Has a pet] ) = "Yes",
        MAXX ( FILTER ( _vtable, [_ID2] = _ID ), [_Email] )
    )

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Bipin-Lala]

Hi @83-guy,

As @Ashish_Mathur mentioned, you can use the RELATED function to pull the email addresses from Table 2 to Table 1 for people having a pet.