Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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?
Solved! Go to 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.
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.
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.
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.
Is this possible in a DAX query? Essentially, I wish to have power automate run a query to grab the email addresses.
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.
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.